2018-12-14
目标：做成一个比较通用的 sql 操作
import com.ljs.util.JDBCUtil;
类名：JdbcUtilDao
1： 更新操作， 针对任何表，增加，删除，更新操作都可以满足。
知识点： 可变数组， preparedstatement 对象。（？） 占位符。
public int update(String sql, Object ...objects){ //
// 1 获取conn 连接
Connection connection =null;
PreparedStatement pt = null;
try {
connection = JDBCUtil.getConn();
pt = connection.prepareStatement(sql);
for( int i =; i < objects.length; ++i ){
pt.setObject(i+, objects[i]);
}
int len = pt.executeUpdate();
return len;
} catch ( Exception e) {
e.printStackTrace();
return -;
} finally {
// 3 关闭 con
JDBCUtil.close(null, pt, connection);
}
}
2： 通用的查询单条记录， 只返回一条的记录。 这条记录用 javabean 对象封装好。
知识点：泛型，ResultSetMetaData 对象保存结果集的属性结构。 和根据ResultSet 的值
反射， 生成对象，和获取对象的属性并且赋值。
ResultSet --> bean.
public<T> T queryOne(Class<T> clazz, String sql, Object ...Object){
try {
Connection connection = JDBCUtil.getConn();
PreparedStatement pst = connection.prepareStatement(sql);
for( int i=; i<Object.length; ++i ){
pst.setObject(i+, Object[i]);
}
ResultSet resultSet = pst.executeQuery();
// ResultSetMetaData 保存这个结果集的属性结构。
ResultSetMetaData metaData = resultSet.getMetaData();
int len = metaData.getColumnCount();
while( resultSet.next() ){
T bean = clazz.newInstance();
for( int i = ; i<=len; ++i ){
String column_name = metaData.getColumnLabel(i);
Object value = resultSet.getObject(column_name);
/*
* 根据结果集的属性结构 构造 属性结构。
* 反射 。
*/
Field field = clazz.getDeclaredField(column_name);
field.setAccessible(true);
field.set(bean, value);
}
return bean;
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
3 查询多条记录，返回list容器列表中。
知识点： 和上一样。 ArrayList
public <T> List<T> queryMutil(Class<T> clazz, String sql, Object ...Object){
try {
Connection connection = JDBCUtil.getConn();
PreparedStatement pst = connection.prepareStatement(sql);
List<T> list = new ArrayList<T>();
for( int i=; i<Object.length; ++i ){
pst.setObject(i+, Object[i]);
}
ResultSet set = pst.executeQuery();
ResultSetMetaData metadata = set.getMetaData();
int len = metadata.getColumnCount();
while(set.next()){
T bean = clazz.newInstance();
for (int i = ; i <= len; i++) {
String name = metadata.getColumnLabel(i);
Object object2 = set.getObject(name);
Field field = clazz.getDeclaredField(name);
field.setAccessible(true);
field.set(bean, object2);
}
list.add(bean);
}
return list;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
上面的测试代码
package com.ljs.daoTest; import java.util.List; import org.junit.Test; import com.ljs.bean.UserBean;
import com.ljs.dao.JdbcUtilDao; public class JdbcDaoTest { @Test
public void update(){ JdbcUtilDao dao = new JdbcUtilDao();
//String sql = "insert into user(name,password) values(?,?)";
String sql = "update user set name = ? where id = 11";
int len = dao.update(sql, "lijinsheng");
System.out.println(len>?"yes":"no");
} @Test
public void queryOne(){
JdbcUtilDao dao = new JdbcUtilDao();
String sql = "select * from user where name= ?";
UserBean tBean = dao.queryOne(UserBean.class, sql,"lijinsheng");
System.out.println(tBean); } @Test
public void queryMutil(){
JdbcUtilDao dao = new JdbcUtilDao();
List<UserBean> list;
String sql = "select * from user where name= ?";
list = dao.queryMutil(UserBean.class, sql, "lijinsheng");
for( int i=; i<list.size(); ++i ){
System.out.println(list.get(i));
}
} }
public class DaoImp implements Dao{
@Override
public UserBean query(int id) throws SQLException {
String sql = "select * from USER where id = ?";
Connection connection = JDBCUtil.getConn();
UserBean bean = new UserBean();
PreparedStatement preparedStatement = (PreparedStatement)
connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
ResultSet rs = preparedStatement.executeQuery();
while(rs.next()){
bean.setId(rs.getInt(1));
bean.setName(rs.getString(2));
bean.setPassword(rs.getString(3));
bean.setPhone(rs.getString(4));
}
JDBCUtil.close(connection,preparedStatement, rs);
return bean;
}
@Override
public void modify(UserBean userBean) throws SQLException {
String sql = "update user set name=?, password=?, phone=? where id=?";
Connection connection = JDBCUtil.getConn();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,userBean.getName());
preparedStatement.setString(2, userBean.getPassword());
preparedStatement.setString(3,userBean.getPhone());
preparedStatement.setInt(4,userBean.getId());
preparedStatement.executeUpdate();
}
@Override
public void add(UserBean user) throws SQLException {
String sql = "insert into user(id,name,password,phone) values(?,?,?,?)";
Connection connection = JDBCUtil.getConn();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,user.getId());
preparedStatement.setString(2,user.getName());
preparedStatement.setString(3,user.getPhone());
preparedStatement.setString(4,user.getPassword());
preparedStatement.executeUpdate();
}
@Override
public void delete(int id) throws SQLException {
String sql = "delete from user where id=?";
Connection connection = JDBCUtil.getConn();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
preparedStatement.executeUpdate();
}
}