简易 DBUtil 封装

package com.util.db; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.sql.Statement; 8 import java.util.ResourceBundle; 9 10 import javax.management.loading.PrivateClassLoader; 11 12 import org.omg.CORBA.PRIVATE_MEMBER; 13 14 /** 15 * 连接数据库 16 * @author Administrator 17 * 18 */ 19 public class DBConn { 20 public static String URL; 21 public static String USERNAME; 22 public static String PASSWORD; 23 public static String DRIVER; 24 private static ResourceBundle resourceBundle = ResourceBundle.getBundle("com.util.db.db_config"); 25 private DBConn() {} 26 /** 27 *为连接数据库变量赋值 28 *加载jdbc驱动 29 */ 30 static{ 31 URL = resourceBundle.getString("jdbc.url"); 32 USERNAME = resourceBundle.getString("jdbc.username"); 33 PASSWORD = resourceBundle.getString("jdbc.password"); 34 DRIVER = resourceBundle.getString("jdbc.driver"); 35 try { 36 Class.forName(DRIVER); 37 } catch (ClassNotFoundException e) { 38 e.printStackTrace(); 39 } 40 } 41 /** 42 * 连接数据库，若连接失败则返回空 43 * @return 44 */ 45 public static Connection getConnection() { 46 Connection conn = null; 47 try { 48 conn = DriverManager.getConnection(URL, USERNAME,PASSWORD); 49 System.out.println("连接成功！"); 50 } catch (SQLException e) { 51 e.printStackTrace(); 52 System.out.println("连接失败"); 53 } 54 return conn; 55 } 56 /** 57 * 关闭数据库连接 58 * @param rs 59 * @param st 60 * @param conn 61 */ 62 public static void close(ResultSet rs,Statement st,Connection conn){ 63 try { 64 if (rs != null) {rs.close();} 65 if (st != null) {st.close();} 66 if (conn !=null) {conn.close();} 67 } catch (SQLException e) { 68 e.printStackTrace(); 69 } 70 71 } 72 public static void main(String[] args) { 73 System.out.println(URL); 74 System.out.println(USERNAME); 75 System.out.println(PASSWORD); 76 System.out.println(DRIVER); 77 78 } 79 }

连接所需配置文件如下：

jdbc.url = jdbc\:mysql\://localhost\:3306/test?characterEncoding\=utf8
jdbc.username = root
jdbc.password = 000000
jdbc.driver =com.mysql.jdbc.Driver

2.对增删该查基本操作做定义  使用不定参数 来解决增删改的参数不同

 1 package com.util.db;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 
 8 /**
 9  * 为数据库操作提供方法模板
10  * @author Administrator
11  *
12  */
13 public class JdbcTemplete {
14     /**
15      * 更新操作
16      * 返回 int 类型的操作行数 用作判断操作成功与否
17      * 若操作异常  则返回 -1
18      * @param sql
19      * @param args
20      * @return
21      */
22 public int update(String sql,Object...args){
23     Connection conn = null;
24     PreparedStatement ps =  null;
25     conn = DBConn.getConnection();
26     try {
27         ps = conn.prepareStatement(sql);
28         if(args!=null){
29             for(int i = 0; i< args.length; i++){
30             ps.setObject(i+1, args[i]);    
31             }
32         }
33       
34         int rs =  ps.executeUpdate();
35         System.out.println("操作成功！");
36         return rs;
37     } catch (SQLException e) {
38         e.printStackTrace();
39 //        try {
40 //            //事务回滚
41 //            conn.rollback();
42 //        } catch (SQLException e1) {e1.printStackTrace();}
43         System.out.println("更新异常");
44         return -1;
45     }finally{
46         DBConn.close(null, ps, conn);
47     }    
48 }
49 /**
50  *  查询操作 不做结果处理
51  *  结果处理放在  ResultSetHandler 
52  *  由调用时编写匿名类  采用策略模式  使用户采用不同策略 处理不同对象
53  * @param sql
54  * @param handler
55  * @param args
56  * @return
57  */
58 public Object query(String sql, ResultSetHandler handler, Object...args) {
59     Connection conn = null;
60     PreparedStatement ps = null;
61     ResultSet rs = null;
62     try {
63         conn = DBConn.getConnection();
64         ps = conn.prepareStatement(sql);
65         if (args != null) {
66             for (int i = 0; i < args.length; i++) {
67                 ps.setObject(i+1, args[i]);
68             }
69         }
70         rs = ps.executeQuery();
71         return handler.doHandler(rs);
72     } catch (SQLException e) {
73         e.printStackTrace();
74         return null;
75     }finally{
76         DBConn.close(rs, ps, conn);
77     }
78 }
79 }

3.对操作查询结果作出接口声明  使用策略模式  对于不同的javaBean有不同的处理

package com.util.db;

import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * 操作连接数据库的结果集
 * 声明接口可以对具体的实体做不同处理
 * @author Administrator
 *
 */
public interface ResultSetHandler {
public Object doHandler(ResultSet rs) throws SQLException;
}

4.定义javaBean

 1 package com.domain;
 2 
 3 public class Person {
 4 private int id;
 5 private String name;
 6 private int age;
 7 private String description;
 8 public Person() {
 9     super();
10 }
11 public Person(String name, int age, String description) {
12     super();
13     this.name = name;
14     this.age = age;
15     this.description = description;
16 }
17 public Person(int id, String name, int age, String description) {
18     super();
19     this.id = id;
20     this.name = name;
21     this.age = age;
22     this.description = description;
23 }
24 public int getId() {
25     return id;
26 }
27 public void setId(int id) {
28     this.id = id;
29 }
30 public String getName() {
31     return name;
32 }
33 public void setName(String name) {
34     this.name = name;
35 }
36 public int getAge() {
37     return age;
38 }
39 public void setAge(int age) {
40     this.age = age;
41 }
42 public String getDescription() {
43     return description;
44 }
45 public void setDescription(String description) {
46     this.description = description;
47 }
48 @Override
49 public String toString() {
50     return "Person [id=" + id + ", name=" + name + ", age=" + age
51             + ", description=" + description + "]";
52 }
53 
54 }

5. 定义Dao  对具体操作  声明接口

 1 package com.dao;
 2 
 3 import java.sql.SQLException;
 4 import java.util.List;
 5 
 6 import com.domain.Person;
 7 
 8 /**
 9  * 用户功能接口
10  * @author Administrator
11  *
12  */
13 public interface PersonDao {
14 public void add(Person person)throws SQLException;
15 public void update(Person person)throws SQLException;
16 public void delete (int id)throws SQLException;
17 public Person findById(int id)throws SQLException;
18 public List findAll()throws SQLException;
19 
20 
21 } 

6.对用户的操作进行实现

 1 package com.dao.impl;
 2 
 3 import java.sql.ResultSet;
 4 import java.sql.SQLException;
 5 import java.util.ArrayList;
 6 import java.util.List;
 7 
 8 import javax.management.j2ee.statistics.JDBCConnectionPoolStats;
 9 
10 import com.dao.PersonDao;
11 import com.domain.Person;
12 import com.util.db.JdbcTemplete;
13 import com.util.db.ResultSetHandler;
14 
15 public class PersonDaoImpl implements PersonDao {
16 private JdbcTemplete jdbcTemplete;
17 public PersonDaoImpl(){
18     jdbcTemplete = new JdbcTemplete();
19 }
20     /**
21      * 添加
22      */
23     @Override
24     public void add(Person person) throws SQLException {
25        String sql = "insert into person(name,age,description)value(?,?,?)";
26        jdbcTemplete.update(sql, person.getName(),person.getAge(),person.getDescription());
27     
28     }
29     /**
30      * 更新操作
31      */
32     @Override
33     public void update(Person person) throws SQLException {
34            String sql ="update person set name=?,age=?,description=? where id=?";
35            jdbcTemplete.update(sql, person.getName(),person.getAge(),person.getDescription(),person.getId());
36     }
37 
38     @Override
39     public void delete(int id) throws SQLException {
40         String sql = "delete from person where id = ?";
41         jdbcTemplete.update(sql,id);
42     }
43 
44     @Override
45     public Person findById(final int id) throws SQLException {
46         String sql = "select name,age,description from person where id =?";
47           return (Person) jdbcTemplete.query(sql, new ResultSetHandler() {
48             @Override
49             public Object doHandler(ResultSet rs) throws SQLException {
50                 Person person = null;
51                 if (rs.next()) {
52                     person = new Person();
53                     person.setId(rs.getInt(id));
54                     person.setName(rs.getString(1));
55                     person.setAge(rs.getInt(2));
56                     person.setDescription(rs.getString(3));
57                 }
58                 return person;
59             }
60         },id );
61     
62     }
63 
64     @Override
65     public List findAll() throws SQLException {
66         String sql = "select id,name,age,description from person";
67         return (List) jdbcTemplete.query(sql,new ResultSetHandler() {
68             @Override
69             public Object doHandler(ResultSet rs) throws SQLException {
70                 List<Person> personList = new ArrayList<>();
71                 Person person =null;
72                 while (rs.next()) {
73                     person = new Person();
74                     person.setId(rs.getInt(1));
75                     person.setName(rs.getString(2));
76                     person.setAge(rs.getInt(3));
77                     person.setDescription(rs.getString(4));
78                     personList.add(person);
79                 }
80                 return personList;
81             }
82         });
83         
84     }
85 
86 }

7.编写测试类

 1 package com.test;
 2 
 3 import java.sql.SQLException;
 4 import java.util.List;
 5 
 6 import com.dao.PersonDao;
 7 import com.dao.impl.PersonDaoImpl;
 8 import com.domain.Person;
 9 
10 public class Test {
11     /**
12      * 
13      * @param args
14      */
15 public static void main(String[] args) {
16     PersonDao personDao = new PersonDaoImpl();
17 
18     try {
19 
20 //        personDao.add(new Person("CCC", 13, "CCC"));
21         
22         personDao.delete(3);
23 //        
24 //        personDao.update(new Person(3, "DDD", 10, "DDD"));
25 //        
26 //        
27 //        Person p4 = personDao.findById(2);
28 //        System.out.println(p4.toString());
29 //        
30 //        
31 //        List<Person> personList = personDao.findAll();
32 //        for (Person p : personList) {
33 //            System.out.println(p.toString());
34 //        }
35         
36     } catch (SQLException e) {
37         e.printStackTrace();
38     }
39 }
40 }

 

简易 DBUtil 封装

标签：loader   定义   delete   stat   ima   ini   []   ade   url