(在上一篇文章中，我们详细的介绍了连接数据库的方法，以及eclipse操作数据库信息的相关方法，在这里我们将主要讲封装。)
主要内容：
- 一般的连接数据库测试
- 把连接数据库的方法封装成一个类和测试
- 一个简单的插入表实例
- 查询数据实例
- 封装查询的数据库的信息
- 封装信息后的查询数据库
一.一般的数据库连接测试
public class TestConnection1 {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/test?"//数据库url
+ "useUnicode=true&characterEncoding=UTF8";//防止乱码
String user="h4";
String pass="111";
Connection conn=DriverManager.getConnection(url, user, pass);
System.out.println(conn+",成功连接数据库");
conn.close();
}
}
二.我们不可能每写一个处理信息功能就写一次连接，这样太麻烦，那么为了方便以后的应用，我们通常把数据库连接封装起来。
具体实现步骤如下：
1.定义变量：
private static String DRIVER_CLASS;
private static String URL;
private static String USERRNAME;
private static String PASSWORD;
2.在你建的eclipse根目录下新建一个File文件Properties；
文件内容为你定义的变量所指向的对象：
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test? useUnicode=true&characterEncoding=UTF8
user=h4
pass=111
3.构建一个Properties对象：Properties p=new Properties();
4. java.io下的类FileInputStream的方法；FileInputStream(String name) ：通过打开一个到实际文件的连接来创建一个 FileInputStream，该文件通过文件系统中的路径名 name 指定。
来获取这个文件里面的资料：FileInputStream fis=new FileInputStream("db.properties");
5. 用3构建的变量p来下载资料：p.load(fis);
6.利用getProperty();获取参数：
DRIVER_CLASS=p.getProperty("driver");
URL=p.getProperty("url");
USERRNAME=p.getProperty("user");
PASSWORD=p.getProperty("pass");
7.写一个连接数据库的方法getConection()；
8.写一个关闭数据库的方法close(Connection conn)；
写好后代码如下：
public class jdbcutil {
private static String DRIVER_CLASS;
private static String URL;
private static String USERRNAME;
private static String PASSWORD;
private static Properties p=new Properties();
static{
try {
FileInputStream fis=new FileInputStream("db.properties");
p.load(fis);
DRIVER_CLASS=p.getProperty("driver");
URL=p.getProperty("url");
USERRNAME=p.getProperty("user");
PASSWORD=p.getProperty("pass");
Class.forName(DRIVER_CLASS);
fis.close();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConection(){
Connection conn=null;
try{
conn=DriverManager.getConnection(URL, USERRNAME, PASSWORD);
}
catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn) {
try {
if (conn != null)
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
那么封装好之后，我们来写一个测试类，测试连接
public class TestConnection2 {
public static void main(String[] args) throws Exception {
Connection conn=jdbcutil.getConection();//利用封装好的类名来调用连接方法便可
System.out.println(conn+",成功连接数据库");
jdbcutil.close( conn);//同样利用类名调用关闭方法即可
}
}
三.连接成功，我们写一个简单的向数据库插入表的实例。
public class TestDDl {
public static void main(String[] args) {
Connection conn=null;
Statement stmt=null;
conn=jdbcutil.getConection();//连接数据库
String createTableSql= " create table user_test1( "+//记住引号和单词间一定要有空格
" id int, "+
" name varchar(32) , "+
" password varchar(32) , "+
" birthday date "+
" ) ";
try {
stmt=conn.createStatement();
stmt.execute(createTableSql);
} catch (SQLException e) {
e.printStackTrace();
}
jdbcutil.close(null, stmt, conn);//关闭数据库
}
}
四.我们在写一个查询数据库数据的实例。(有三种方法)
public class TestDQL {
public static void main(String[] args){
Connection conn=null;//定义为空值
Statement stmt=null;
ResultSet rs=null;
String sql="select * from employees";//sql语句
conn=jdbcutil.getConection();
try {
stmt=conn.createStatement();//创建一个Statement语句对象
rs=stmt.executeQuery(sql);//执行sql语句
while(rs.next()){
System.out.print(rs.getInt(1)+",");
System.out.print(rs.getString(2)+",");//直接使用参数
System.out.print(rs.getString(3)+",");
System.out.print(rs.getString(4)+",");
System.out.println(rs.getString(5));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbcutil.close(rs,stmt,conn);//关闭数据库
}
}
}
//第二种方法如下：
public class TestDQl2 {
public static void main(String[] args) {
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
String sql="select * from employees";
conn=jdbcutil.getConection();
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
System.out.print(rs.getInt("userid")+",");//里面直接写要查找的内容名称
System.out.print(rs.getString("employee_id")+",");
System.out.print(rs.getString("last_name")+",");
System.out.print(rs.getString("salary")+",");
System.out.println(rs.getString("department_id"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbcutil.close(rs,stmt,conn);
}
}
}
//第三种方法如下：
public class TestDQL3 {
public static void main(String[] args) {
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
String sql="select * from employees";
conn=jdbcutil.getConection();
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
int index=1;
System.out.print(rs.getInt(index++)+",");
System.out.print(rs.getString(index++)+",");
System.out.print(rs.getString(index++)+",");
System.out.print(rs.getString(index++)+",");
System.out.println(rs.getString(index++));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbcutil.close(rs,stmt,conn);
}
}
}
五.在四里面我们写了查询员工资料的信息，但是有的时候我们要保存起来方便之后更好的查找，那怎么办呢？没错，封装。
public class employees implements Serializable {
private Integer userid;
private String employee_id;
private String last_name;
private String salary;
private String department_id;
public employees() {
super();
}
public employees(String employee_id, String last_name, String salary, String department_id) {
super();
this.employee_id = employee_id;
this.last_name = last_name;
this.salary = salary;
this.department_id = department_id;
}
@Override
public String toString() {
return "employees [userid=" + userid + ", employee_id=" + employee_id + ", last_name=" + last_name
+ ", salary=" + salary + ", department_id=" + department_id + "]";
}
public Integer getUserid() {
return userid;
}
public void setUserid(Integer userid) {
this.userid = userid;
}
public String getEmployee_id() {
return employee_id;
}
public void setEmployee_id(String employee_id) {
this.employee_id = employee_id;
}
public String getLast_name() {
return last_name;
}
public void setLast_name(String last_name) {
this.last_name = last_name;
}
public String getSalary() {
return salary;
}
public void setSalary(String salary) {
this.salary = salary;
}
public String getDepartment_id() {
return department_id;
}
public void setDepartment_id(String department_id) {
this.department_id = department_id;
}
}
六.封装好后的查询和上面没封装之前有点变化。
public class TestDQL4 {
public static void main(String[] args) {
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
List<employees> emps=new ArrayList<>();//构造集合对象
String sql="select * from employees";
conn=jdbcutil.getConection();//获取数据库连接
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){//遍历结果集
int index=1;
employees emp=new employees();//构造员工类对象
emp.setUserid(rs.getInt(index++));//获取值
emp.setEmployee_id(rs.getString(index++));
emp.setLast_name(rs.getString(index++));
emp.setSalary(rs.getString(index++));
emp.setDepartment_id(rs.getString(index++));
emps.add(emp);//放到集合中去
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbcutil.close(rs,stmt,conn);//关闭连接
}
for(employees emp:emps){//遍历
System.out.println(emp);
}
}
}
其实我们可以继续封装，把遍历结果集给封装起来。
public class TestDQL5 {
public static void main(String[] args) {
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
List<employees> emps=new ArrayList<>();
String sql="select * from employees";
conn=jdbcutil.getConection();
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
emps=resultSetToEmployees(rs);
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbcutil.close(rs,stmt,conn);
}
for(employees emp:emps){
System.out.println(emp);
}
}
public static List<employees> resultSetToEmployees(ResultSet rs){
List<employees> emps=new ArrayList<>();
try {
while(rs.next()){
int index=1;
employees emp=new employees();
emp.setUserid(rs.getInt(index++));
emp.setEmployee_id(rs.getString(index++));
emp.setLast_name(rs.getString(index++));
emp.setSalary(rs.getString(index++));
emp.setDepartment_id(rs.getString(index++));
emps.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}
return emps;
}
}
如果是一个人查询信息呢？还可以这样封装。
public class TestDQL6 {
public static void main(String[] args) {
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
List<employees> emps=new ArrayList<>();
String sql="select * from employees";
conn=jdbcutil.getConection();
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
employees emp=resultSetToEmployee(rs);
emps.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbcutil.close(rs,stmt,conn);
}
for(employees emp:emps){
System.out.println(emp);
}
}
public static employees resultSetToEmployee(ResultSet rs){
employees emp=null;
try {
int index=1;
emp=new employees();
emp.setUserid(rs.getInt(index++));
emp.setEmployee_id(rs.getString(index++));
emp.setLast_name(rs.getString(index++));
emp.setSalary(rs.getString(index++));
emp.setDepartment_id(rs.getString(index++));
} catch (SQLException e) {
e.printStackTrace();
}
return emp;
}
}