一:版本一.这种存在一个问题就是每执行一次操作都会创建一次Connection链接和且释放一次链接
1:创建pojo对象(OR映射,一个pojo类对应一张数据库表)
1 packagecom.yinfu.dao;2
3 public classEmployee {4
5 private intid;6 privateString name;7 privateString password;8 public intgetId() {9 returnid;10 }11 public void setId(intid) {12 this.id =id;13 }14 publicString getName() {15 returnname;16 }17 public voidsetName(String name) {18 this.name =name;19 }20 publicString getPassword() {21 returnpassword;22 }23 public voidsetPassword(String password) {24 this.password =password;25 }26 @Override27 publicString toString() {28 return "Employee [id=" + id + ", name=" + name + ", password=" + password + "]";29 }30 public Employee(intid, String name, String password) {31 super();32 this.id =id;33 this.name =name;34 this.password =password;35 }36 publicEmployee() {37 super();38 }39 }
pojo对象
2:创建数据库连接用的数据文件,用于外界读取数据(properties文件):
1 driver=com.mysql.jdbc.Driver2 jdbcUrl=jdbc:mysql://localhost:3306/test 3 user=root4 password=song12345
3:创建数据库连接和关闭连接的工具类(被重复使用的方法可以写在工具类中):
1 packagecom.yinfu.utils;2
3 importjava.io.IOException;4 importjava.io.InputStream;5 importjava.sql.Connection;6 importjava.sql.DriverManager;7 importjava.sql.PreparedStatement;8 importjava.sql.ResultSet;9 importjava.sql.SQLException;10 importjava.util.Properties;11 /**
12 * JDBC的工具类,封装了jdbc的一些方法13 *@authorlusong14 *15 */
16 public classJDBCUtils {17
18 //关闭jdbc的链接
19 /**
20 * 关闭statement和connection21 *@paramps22 *@paramconn23 */
24 public static voidrelease(PreparedStatement ps, Connection conn){25 try{26 if(ps != null){27 ps.close();28 }29 } catch(SQLException e) {30 e.printStackTrace();31 }finally{32 try{33 if(conn != null){34 conn.close();35 }36 } catch(SQLException e) {37 e.printStackTrace();38 }39 }40 }41 public static voidrelease(ResultSet result,PreparedStatement ps, Connection conn){42 try{43 if(result != null){44 result.close();45 }46 } catch(SQLException e1) {47 e1.printStackTrace();48 }finally{49 try{50 if(ps != null){51 ps.close();52 }53 } catch(SQLException e) {54 e.printStackTrace();55 }finally{56 try{57 if(conn != null){58 conn.close();59 }60 } catch(SQLException e) {61 e.printStackTrace();62 }63 }64 }65
66 }67
68 //获取jdbc的链接
69 /**
70 * 用于创建jdbc链接的工具类对象71 *@return
72 */
73 public staticConnection getConnetions() {74 Connection conn = null;75 String driverClass = null;76 String jdbcUrl = null;77 String user = null;78 String password = null;79
80 try{81 //读取配置文件中的配置
82 InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");83 Properties properties = newProperties();84 properties.load(is);85 driverClass = properties.getProperty("driver");86 jdbcUrl = properties.getProperty("jdbcUrl");87 user = properties.getProperty("user");88 password = properties.getProperty("password");89 //注册驱动程序
90 Class.forName(driverClass);91 //实际应该这样写(由于对应的应用程序中有一个对应的静态代码块,自动回将驱动的类对象进行驱动加载)92 //DriverManager.registerDriver((Driver) Class.forName(driverClass).newInstance());
93
94 conn =DriverManager.getConnection(jdbcUrl,user,password);95
96 } catch(IOException e) {97 //TODO Auto-generated catch block
98 e.printStackTrace();99 }catch(SQLException e) {100 //TODO Auto-generated catch block
101 e.printStackTrace();102 }catch(ClassNotFoundException e) {103 //TODO Auto-generated catch block
104 e.printStackTrace();105 }106 returnconn;107 }108 }
4:用Junit测试实现的JDBC实现数据库的增删改查操作:
1 packagecom.yinfu.test;2
3 importjava.io.IOException;4 importjava.io.InputStream;5 importjava.sql.Connection;6 importjava.sql.DriverManager;7 importjava.sql.PreparedStatement;8 importjava.sql.ResultSet;9 importjava.sql.SQLException;10 importjava.util.ArrayList;11 importjava.util.List;12 importjava.util.Properties;13
14 importorg.junit.Test;15
16 importcom.yinfu.dao.Employee;17 importcom.yinfu.utils.JDBCUtils;18
19 public classJDBCTest {20
21 @Test22 public voidtestUpdate(){23 //曾
24 String sql = "insert into employee (Id,Name,Password) values (1,'wangba',131)";25 //删26 //String sql = "delete from employee where id = 1";27 //改28 //String sql = "update employee set name = 'fuck' where id = 2";29 //查
30 String sqlQuery = "select * from employee";31 update(sql);32 testQueryObject(sqlQuery);33 }34
35 public voidtestQueryObject(String sql){36 Employee employee = null;37 List list = newArrayList();38 Connection conn = null;39 PreparedStatement ps = null;40 ResultSet result = null;41 try{42 //创建连接
43 conn =JDBCUtils.getConnetions();44 //创建prepareStatement对象,用于执行SQL
45 ps =conn.prepareStatement(sql);46 //获取查询结果集
47 result =ps.executeQuery();48 while(result.next()){49 employee = new Employee(result.getInt(1),result.getString(2),result.getString(3));50 list.add(employee);51 }52 System.out.println(list);53 } catch(Exception e) {54 e.printStackTrace();55 }finally{56 JDBCUtils.release(result, ps, conn);57 }58 }59
60 public voidupdate(String sql){61 Connection conn = null;62 PreparedStatement ps = null;63 try{64 //创建数据库连接
65 conn =JDBCUtils.getConnetions();66 //创建执行SQL的prepareStatement对象
67 ps =conn.prepareStatement(sql);68 //用于增删改操作
69 int result =ps.executeUpdate();70 System.out.println(result);71 } catch(Exception e) {72 System.out.println("出现异常1="+e.toString());73 }finally{74 JDBCUtils.release(ps, conn);75 }76
77
78 }79 }
Statement 和PrepareStatement的区别:
首先是执行SQL的方法:
statement:
Class.forName(jdbcDriver);
Connection conn = DriverManager.getConnection(jdbcUrl,userName,password);
String sql = "insert into employee () values ('','','')"
Statement statement = conn.createStatement();
statement.executeUpdate(sql);
其中的SQL语句中若有要动态输入的数据时,需要用字符串拼接SQL,难以维护容易出错。
prepareStatement:
Class.forName(jdbcDriver);
Connection conn = DriverManager.getConnection(jdbcUrl,userName,password);
String sql = "insert into employee () values ('','','')"
PrepareStatement ps = conn.prepareStatement(sql);
statement.executeUpdate();
其中的SQL语句中要是有动态输入的数据时,可以用占位'?'符来代替:
String sql = "insert into employee () values (?,?,?)";
然后用prepareStatement接口中的方法来动态赋值:
ps.setXXX(int paramIndex ,Object value);//参数含义:占位符对应的索引值,该索引值对应的参数值;
2:(利用反射工具类)升级版查询:利用反射和JDBC元数据编写通用的查询单条记录方法(ResultSetMetaData是结果集的元数据对象):
1:创建反射工具类:
1 packagecom.yinfu.utils;2
3 importjava.lang.reflect.Field;4 importjava.lang.reflect.InvocationTargetException;5 importjava.lang.reflect.Method;6 importjava.lang.reflect.Modifier;7 importjava.lang.reflect.ParameterizedType;8 importjava.lang.reflect.Type;9
10 /**
11 * 反射的 Utils 函数集合 提供访问私有变量, 获取泛型类型 Class, 提取集合中元素属性等 Utils 函数12 *13 *@authorAdministrator14 *15 */
16 public classReflectionUtils {17
18 /**
19 * 通过反射, 获得定义 Class 时声明的父类的泛型参数的类型 如: public EmployeeDao extends20 * BaseDao21 *22 *@paramclazz23 *@paramindex24 *@return
25 */
26 @SuppressWarnings("unchecked")27 public static Class getSuperClassGenricType(Class clazz, intindex) {28 Type genType =clazz.getGenericSuperclass();29
30 if (!(genType instanceofParameterizedType)) {31 return Object.class;32 }33
34 Type[] params =((ParameterizedType) genType).getActualTypeArguments();35
36 if (index >= params.length || index < 0) {37 return Object.class;38 }39
40 if (!(params[index] instanceofClass)) {41 return Object.class;42 }43
44 return(Class) params[index];45 }46
47 /**
48 * 通过反射, 获得 Class 定义中声明的父类的泛型参数类型 如: public EmployeeDao extends49 * BaseDao50 *51 *@param52 *@paramclazz53 *@return
54 */
55 @SuppressWarnings("unchecked")56 public static ClassgetSuperGenericType(Class clazz) {57 return getSuperClassGenricType(clazz, 0);58 }59
60 /**
61 * 循环向上转型, 获取对象的 DeclaredMethod62 *63 *@paramobject64 *@parammethodName65 *@paramparameterTypes66 *@return
67 */
68 public static Method getDeclaredMethod(Object object, String methodName, Class>[] parameterTypes) {69
70 for (Class> superClass = object.getClass(); superClass != Object.class; superClass =superClass71 .getSuperclass()) {72 try{73 //superClass.getMethod(methodName, parameterTypes);
74 returnsuperClass.getDeclaredMethod(methodName, parameterTypes);75 } catch(NoSuchMethodException e) {76 //Method 不在当前类定义, 继续向上转型
77 }78 //..
79 }80
81 return null;82 }83
84 /**
85 * 使 filed 变为可访问86 *87 *@paramfield88 */
89 public static voidmakeAccessible(Field field) {90 if (!Modifier.isPublic(field.getModifiers())) {91 field.setAccessible(true);92 }93 }94
95 /**
96 * 循环向上转型, 获取对象的 DeclaredField97 *98 *@paramobject99 *@paramfiledName100 *@return
101 */
102 public staticField getDeclaredField(Object object, String filedName) {103
104 for (Class> superClass = object.getClass(); superClass != Object.class; superClass =superClass105 .getSuperclass()) {106 try{107 returnsuperClass.getDeclaredField(filedName);108 } catch(NoSuchFieldException e) {109 //Field 不在当前类定义, 继续向上转型
110 }111 }112 return null;113 }114
115 /**
116 * 直接调用对象方法, 而忽略修饰符(private, protected)117 *118 *@paramobject119 *@parammethodName120 *@paramparameterTypes121 *@paramparameters122 *@return
123 *@throwsInvocationTargetException124 *@throwsIllegalArgumentException125 */
126 public static Object invokeMethod(Object object, String methodName, Class>[] parameterTypes, Object[] parameters)127 throwsInvocationTargetException {128
129 Method method =getDeclaredMethod(object, methodName, parameterTypes);130
131 if (method == null) {132 throw new IllegalArgumentException("Could not find method [" + methodName + "] on target [" + object + "]");133 }134
135 method.setAccessible(true);136
137 try{138 returnmethod.invoke(object, parameters);139 } catch(IllegalAccessException e) {140 System.out.println("不可能抛出的异常");141 }142
143 return null;144 }145
146 /**
147 * 直接设置对象属性值, 忽略 private/protected 修饰符, 也不经过 setter148 *149 *@paramobject150 *@paramfieldName151 *@paramvalue152 */
153 public static voidsetFieldValue(Object object, String fieldName, Object value) {154 Field field =getDeclaredField(object, fieldName);155
156 if (field == null)157 throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + object + "]");158
159 makeAccessible(field);160
161 try{162 field.set(object, value);163 } catch(IllegalAccessException e) {164 System.out.println("不可能抛出的异常");165 }166 }167
168 /**
169 * 直接读取对象的属性值, 忽略 private/protected 修饰符, 也不经过 getter170 *171 *@paramobject172 *@paramfieldName173 *@return
174 */
175 public staticObject getFieldValue(Object object, String fieldName) {176 Field field =getDeclaredField(object, fieldName);177
178 if (field == null)179 throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + object + "]");180
181 makeAccessible(field);182
183 Object result = null;184
185 try{186 result =field.get(object);187 } catch(IllegalAccessException e) {188 System.out.println("不可能抛出的异常");189 }190
191 returnresult;192 }193 }
反射工具类
2:编写通用查询:
1 packagecom.yinfu.test;2
3 importjava.io.IOException;4 importjava.io.InputStream;5 importjava.sql.Connection;6 importjava.sql.DriverManager;7 importjava.sql.PreparedStatement;8 importjava.sql.ResultSet;9 importjava.sql.ResultSetMetaData;10 importjava.sql.SQLException;11 importjava.util.ArrayList;12 importjava.util.HashMap;13 importjava.util.List;14 importjava.util.Map;15 importjava.util.Properties;16
17 importorg.junit.Test;18
19 importcom.yinfu.dao.Employee;20 importcom.yinfu.utils.JDBCUtils;21 importcom.yinfu.utils.ReflectionUtils;22
23 public classJDBCTest {24
25 @Test26 public voidtestUpdate(){27 //查
28 String sqlQuery = "select id, name, password from employee where name = ?";29 Object employee = testQueryObject(Employee.class,sqlQuery,"zhangsan");30 System.out.println("利用反射="+employee);31 }32
33 public T testQueryObject(Class clazz, String sql, Object ... args){34 T object = null;35 Map map = new HashMap();36 Connection conn = null;37 PreparedStatement ps = null;38 ResultSet resultSet = null;39 try{40 //创建连接
41 conn =JDBCUtils.getConnetions();42 //创建prepareStatement对象,用于执行SQL
43 ps =conn.prepareStatement(sql);44 //将参数赋值到sql的所需参数中
45 for(int i = 0 ; i < args.length ; i++){46 ps.setObject(i+1, args[i]);47 }48 //一:根据SQL语句和传入的参数得到结果集,此结果集中全部是纯数据值,不带列名;
49 resultSet =ps.executeQuery();50 //二:利用ResultSet对象得到ResultSetMetaData对象jdbc的元数据,根据此对象可以知道SQL语句查询了哪些列,以及列的别名是什么(具体参考JDBC的API进行学习)
51 ResultSetMetaData rsmd =resultSet.getMetaData();52 while(resultSet.next()){53 //把列名的别名和列值分别取出来放到map中作为键值出现(resultSet和rsmd结合得到的就是一个表,和数据库表一样),由ResultSetMetaData得到每一列的别名, //由ResultSet 得到对应的值
54 for(int i=0;i
61 object =(T) clazz.newInstance();62 //遍历map对象,用反射填充对象属性值
63 for(Map.Entryentry : map.entrySet()){64 String fieldName =entry.getKey();65 Object fieldValue =entry.getValue();66 //利用反射工具類(属性名对应map的key值,属性名对应map的value值)
67 ReflectionUtils.setFieldValue(object, fieldName, fieldValue);68 }69 } catch(Exception e) {70 e.printStackTrace();71 }finally{72 JDBCUtils.release(resultSet, ps, conn);73 }74 returnobject;75 }76 }
3:利用BeanUtils工具类实现查询多条记录(添加commons-beanutils.jar和commons-logging.jar):
1 packagecom.yinfu.test;2
3 importjava.io.IOException;4 importjava.io.InputStream;5 importjava.sql.Connection;6 importjava.sql.DriverManager;7 importjava.sql.PreparedStatement;8 importjava.sql.ResultSet;9 importjava.sql.ResultSetMetaData;10 importjava.sql.SQLException;11 importjava.util.ArrayList;12 importjava.util.HashMap;13 importjava.util.List;14 importjava.util.Map;15 importjava.util.Properties;16
17 importorg.apache.commons.beanutils.BeanUtils;18 importorg.junit.Test;19
20 importcom.yinfu.dao.Employee;21 importcom.yinfu.utils.JDBCUtils;22 importcom.yinfu.utils.ReflectionUtils;23
24 public classJDBCTest {25
26 @Test27 public voidtestUpdate(){28 //查多条
29 String sqlQueryList = "select id, name, password from employee";30 List testQueryList = testQueryList(Employee.class,sqlQueryList);31 System.out.println("查询多条:"+testQueryList);32
33 }34
35 //查询多条记录
36 public List testQueryList(Classclazz, String sql, Object ...args ){37 //用于接收返回值
38 T object = null;39 List list = new ArrayList<>();40 Connection conn = null;41 PreparedStatement rs = null;42 ResultSet resultSet = null;43 try{44 //获取数据库连接
45 conn =JDBCUtils.getConnetions();46 rs =conn.prepareStatement(sql);47 //填充占位符
48 for(int i = 0; i < args.length; i++){49 rs.setObject(i+1, args[i]);50 }51 //获取结果集
52 resultSet =rs.executeQuery();53 //1:准备一个List>集合,其中key为列名,value为列值,每一个map对应一条记录
54 List> listMap = new ArrayList<>();55 //2:得到jdbc的元数据
56 ResultSetMetaData rsmd =rs.getMetaData();57 while(resultSet.next()){58 Map map = new HashMap<>();59 for(int i = 0; i < rsmd.getColumnCount(); i++){60 //游标是从1开始的
61 String columnLabel = rsmd.getColumnLabel(i+1);62 Object columnValue =resultSet.getObject(columnLabel);63 map.put(columnLabel, columnValue);64 }65 //3:把一条记录map放入到listMap中
66 listMap.add(map);67 }68
69 /*//上面一段代码可以这样写70 List labelList = getColumnLabels(resultSet);71 while(resultSet.next()){72 Map map = new HashMap<>();73 for(String columnLabel : labelList){74 Object columnValue = resultSet.getObject(columnLabel);75 map.put(columnLabel, columnValue);76 }77 //3:把一条记录map放入到listMap中78 listMap.add(map);79 }*/
80
81 //4:遍历listMap集合,把其中的每一个map都转换成对应的Class对象,并放到list中进行返回
82 if(listMap.size()>0){83 for(MapmapObj : listMap){84 //有记录就通过反射得到对应的类对象
85 object =clazz.newInstance();86 for(Map.Entryentry : mapObj.entrySet()){87 String propertyName =entry.getKey();88 Object propertyValue =entry.getValue();89 //利用工具类beanutils进行实体类转换
90 BeanUtils.setProperty(object, propertyName, propertyValue);91 }92 list.add(object);93 }94 }95
96 } catch(Exception e) {97 e.printStackTrace();98 }99
100 returnlist;101 }102
103 private List getColumnLabels(ResultSet resultSet) throwsSQLException{104 ResultSetMetaData rsmd =resultSet.getMetaData();105 List list = new ArrayList<>();106 for(int i = 0; i
查询多条记录
4:可以用获取PrepareStatement的另一个重载方法得到,然后再用此对象的getGeneratedKeys()方法得到插入的数据时自动生成的ID的结果集,此结果集就一列,列名为:GENERATED_K。