依靠反射 手写DButils

闲来无事，写个dbutils玩玩，不完善，满足基本增删改查，上代码

1、Dbutils

  1 package db;
  2 
  3 import annotation.Table;
  4 import java.util.*;
  5 import java.sql.*;
  6 import java.lang.reflect.Field;
  7 
  8 /**
  9  *
 10  * @author xjy
 11  * @param <T>
 12  */
 13 public class DbUtil<T> {
 14 
 15     private static DataBase dataBase = null;
 16     private static Connection con = null;
 17     private static Statement st = null;
 18 
 19     public DbUtil() {
 20         dataBase = new DataBase();
 21     }
 22 
 23     @SuppressWarnings("null")
 24     public List<T> getAll(T bean) throws Exception {
 25 
 26         List<T> list = new ArrayList<>();
 27         Class<T> clazz = (Class<T>) bean.getClass();
 28         Table table = clazz.getDeclaredAnnotation(Table.class);
 29         String name = table.name();
 30 
 31         String tableName;
 32         if (!"".equals(name)) {
 33             tableName = name;
 34         } else {
 35             tableName = clazz.getSimpleName().toLowerCase();
 36         }
 37 
 38         StringBuilder sql = new StringBuilder("select * from " + tableName);
 39 
 40         if (bean != null) {
 41             sql.append(" where ");
 42             Field[] declaredFields = clazz.getDeclaredFields();
 43 
 44             for (Field declaredField : declaredFields) {
 45                 //打开私有访问
 46                 declaredField.setAccessible(true);
 47                 String name1 = declaredField.getName();
 48                 Object value = declaredField.get(bean);
 49                 if (value != null && !"id".equals(name1)) {
 50                     sql.append(name1).append("='").append(value).append("' and ");
 51                 }
 52             }
 53             sql.append("1=1");
 54         }
 55 
 56         con = dataBase.getConnection();
 57         st = dataBase.getStatement();
 58 
 59         ResultSet rs = st.executeQuery(sql.toString());
 60 
 61         Field[] fields = clazz.getDeclaredFields();
 62         while (rs.next()) {
 63 
 64             T t = clazz.newInstance();
 65 
 66             for (Field field : fields) {
 67                 String fName = field.getName();
 68                 String str = rs.getString(fName);
 69                 field.setAccessible(true);
 70 
 71                 // 如果类型是Integer  
 72                 if (field.getGenericType().toString().equals("class java.lang.Integer")) {
 73                     field.set(t, Integer.valueOf(str));
 74                 } else {
 75                     field.set(t, str);
 76                 }
 77 
 78             }
 79 
 80             list.add(t);
 81         }
 82 
 83         return list;
 84     }
 85 
 86     public int add(T bean) throws Exception {
 87         con = dataBase.getConnection();
 88         st = dataBase.getStatement();
 89         Class<T> clazz = (Class<T>) bean.getClass();
 90         Table table = clazz.getDeclaredAnnotation(Table.class);
 91         String name = table.name();
 92 
 93         String tableName;
 94         if (!"".equals(name)) {
 95             tableName = name;
 96         } else {
 97             tableName = clazz.getSimpleName().toLowerCase();
 98         }
 99 
100         Field[] fields = clazz.getDeclaredFields();
101 
102         StringBuilder sql = new StringBuilder("insert into " + tableName + "(");
103 
104         for (Field field : fields) {
105             String fName = field.getName();
106             if (!"id".equals(fName)) {
107                 sql.append(fName).append(",");
108 
109             }
110         }
111 
112         sql.deleteCharAt(sql.length() - 1).append(") values(");
113 
114         Field[] declaredFields = clazz.getDeclaredFields();
115 
116         for (Field declaredField : declaredFields) {
117 
118             if (!"id".equals(declaredField.getName())) {
119                 //打开私有访问
120                 declaredField.setAccessible(true);
121                 Object value = declaredField.get(bean);
122                 sql.append("'").append(value).append("',");
123             }
124 
125         }
126 
127         sql.deleteCharAt(sql.length() - 1).append(")");
128         int result = 0;
129 
130         try {
131             result = st.executeUpdate(sql.toString());
132         } catch (SQLException se) {
133             System.out.println(se.getMessage());
134         } finally {
135 
136         }
137         return result;
138     }
139 
140     @SuppressWarnings("FinallyDiscardsException")
141     public List<Map<String, Object>> querySql(String sql) {
142         List<Map<String, Object>> list = new ArrayList<>();
143 
144         con = dataBase.getConnection();
145         st = dataBase.getStatement();
146         @SuppressWarnings("UnusedAssignment")
147         ResultSet rs = null;
148         try {
149             rs = st.executeQuery(sql);
150             ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据
151             int columnCount = md.getColumnCount();   //获得列数
152             while (rs.next()) {
153                 Map<String, Object> rowData = new HashMap<>();
154                 for (int i = 1; i <= columnCount; i++) {
155                     rowData.put(md.getColumnName(i), rs.getObject(i));
156                 }
157                 list.add(rowData);
158             }
159         } catch (SQLException e) {
160             System.out.println(e.toString());
161 
162         } finally {
163             dataBase.closeConnection();
164             return list;
165         }
166     }
167 
168     @SuppressWarnings("FinallyDiscardsException")
169     public int executeSql(String sql) {
170         con = dataBase.getConnection();
171         st = dataBase.getStatement();
172         int result = 0;
173         try {
174             result = st.executeUpdate(sql);
175         } catch (SQLException se) {
176             System.out.println(se.getMessage());
177         } finally {
178             dataBase.closeConnection();
179             return result;
180         }
181     }
182 
183     @SuppressWarnings("MismatchedReadAndWriteOfArray")
184     public int update(T bean) throws Exception {
185         con = dataBase.getConnection();
186         st = dataBase.getStatement();
187         Class<T> clazz = (Class<T>) bean.getClass();
188         Table table = clazz.getDeclaredAnnotation(Table.class);
189         String name = table.name();
190 
191         String tableName;
192         if (!"".equals(name)) {
193             tableName = name;
194         } else {
195             tableName = clazz.getSimpleName().toLowerCase();
196         }
197 
198         StringBuilder sql = new StringBuilder("update " + tableName + " set ");
199 
200         Field[] declaredFields = clazz.getDeclaredFields();
201 
202         for (Field declaredField : declaredFields) {
203             declaredField.setAccessible(true);
204             //打开私有访问
205             String name1 = declaredField.getName();
206             Object value = declaredField.get(bean);
207             if (value != null && !"id".equals(name1)) {
208                 sql.append(name1).append("='").append(value).append("',");
209             }
210         }
211         Field field1 = clazz.getDeclaredField("id");
212         field1.setAccessible(true);
213 
214         sql.deleteCharAt(sql.length() - 1).append(" where id =").append(field1.get(bean));
215 
216         int result = 0;
217 
218         try {
219             result = st.executeUpdate(sql.toString());
220         } catch (SQLException se) {
221             System.out.println(se.getMessage());
222         } finally {
223         }
224         return result;
225     }
226 }

 

 

2、注解Table

 1 package annotation;
 2 
 3 import java.lang.annotation.ElementType;
 4 import java.lang.annotation.Retention;
 5 import java.lang.annotation.RetentionPolicy;
 6 import java.lang.annotation.Target;
 7 
 8 /**
 9  *
10  * @author xjy
11  */
12 @Target(ElementType.TYPE)
13 @Retention(RetentionPolicy.RUNTIME)
14 public @interface Table {
15 
16     public String name() default "";
17 
18 }

 

 

3、database，获取连接等，这里用的sqlserver，其他自己发挥咯

 1 package db;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.SQLException;
 6 import java.sql.Statement;
 7 
 8 /**
 9  *
10  * @author Administrator
11  */
12 public class DataBase {
13   
14     private String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
15     String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=db_car";
16     private String userName = "sa";
17     private String userPasswd = "123456";
18     private Connection connection;
19     private Statement statement;
20     public DataBase(){
21         try {
22             Class.forName(driverName);
23         }
24         catch (ClassNotFoundException cnfex){
25             System.err.println("装载 JDBC/ODBC 驱动程序失败。");
26             cnfex.printStackTrace();
27             System.exit(1);
28         }
29     } 
30     //取得与数据库的连接
31     public Connection getConnection(){
32         try {
33             //捕获连接数据库异常
34             connection = DriverManager.getConnection(url, userName, userPasswd);
35         } 
36         catch (SQLException sqlex){
37             System.err.println("无法连接数据库");
38             sqlex.printStackTrace();
39             System.exit(1);
40         }
41         finally {
42             return connection;
43         }
44     }
45     //取得 statement
46     public Statement getStatement(){ 
47         try {
48             if (connection != null){
49             statement = connection.createStatement();
50             }
51         } 
52         catch (SQLException sqlex){
53             System.err.println("无法取得 Statement");
54             sqlex.printStackTrace();
55             System.exit(1);
56         }
57         finally{
58             return statement;
59         }
60     }
61     public void closeConnection(){
62         try {
63             if (null != statement){
64                 statement.close(); 
65             }
66             if (null != connection){
67                 connection.close();
68             }
69             statement = null;
70             connection = null;
71             } 
72             catch (Exception e){
73                 e.printStackTrace();
74             }
75         } 
76     }

 

所需驱动文件：https://files.cnblogs.com/files/xujingyang/sqljdbc4.zip

顺便说下，开发工具玩的是NetBeans

posted @ 2019-01-06 23:10  oldmonk  阅读(465)  评论(0)  编辑  收藏  举报
Fork me on GitHub