具体代码:
封装两个方法 :一个简化DQL方法;一个简化非DQL方法
public abstract class BaseDao {
/*
* 封装简化非DQL语句
* sql: 带占位符的SQL语句
* params 占位符的值 注意传入占位符的值,必须等于SQL语句?位置
* return 执行影响的次数
* */
public int executeUpdate(String sql,Object...Params) throws Exception {
//获取连接
Connection connection = JDBC_Utiles_1.getConnection();
PreparedStatement preparedStatement=null;
try {
preparedStatement = connection.prepareStatement(sql);
//占位赋值
//可变参数可以当做数据使用
for (int i = 1; i < Params.length; i++) {
preparedStatement.setObject(i,Params[i-1]);
}
} catch (Exception e) {
e.printStackTrace();
}
//发送sql语句
int i = preparedStatement.executeUpdate();
preparedStatement.close();
//是否回收连接,需要考虑是不是事物
if(connection.getAutoCommit()){
//没有开启事务,正常回收连接
homework.ways.way4.JDBC_Utiles_1.closeAll(connection);
}
connection.setAutoCommit(false);//开启事物了,不要管连接,业务层处理
return i;
}
//封装简化非DQL语句
//将查询结果封装到一个实体类集合
public <T>List<T> executeQuery(Class<T> clazz,String sql,Object...Params) throws Exception{
Connection connection = homework.ways.way4.JDBC_Utiles_1.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
if(Params==null&&Params.length!=0){
for (int i = 1; i < Params.length; i++) {
preparedStatement.setObject(i,Params[i-1]);
}
}
ResultSet resultSet = preparedStatement.executeQuery();
List<T> list = new ArrayList<>();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while(resultSet.next()){
T t = clazz.newInstance();
for(int i=1;i<=columnCount;i++){
//对象的属性值
Object value = resultSet.getObject(i);
//获取指定列下标的列的名称
String columnLabel = metaData.getColumnLabel(i);
//反射,给对象的属性赋值
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,value);
}
list.add(t);
}
resultSet.close();;
preparedStatement.close();
if(connection.getAutoCommit()){
homework.ways.way4.JDBC_Utiles_1.closeAll(connection);
}
return list;
}
}
2、封装连接工具类
public class JDBC_Utiles_1 {
private static String user;
private static String url;
private static String password;
static {
InputStream stream=null;
Properties properties=null;
try {
stream = JDBC_Utiles_1.class.getClassLoader().getResourceAsStream("jdbc.properties");
properties = new Properties();
properties.load(stream);
user = properties.getProperty("user");
password = properties.getProperty("password");
url = properties.getProperty("url");
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
stream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static Connection getConnection(){
Connection connection=null;
try {
connection= DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static void closeAll(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
try {
if(connection !=null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(preparedStatement!=null){
preparedStatement.close();}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(resultSet!=null){
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void closeAll(Connection connection, PreparedStatement preparedStatement){
try {
if(connection !=null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(preparedStatement!=null){
preparedStatement.close();}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void closeAll(Connection connection){
try {
if(connection !=null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3、测试:@Test表示按模块进行测试
public class PSCURDPart extends BaseDao{
@Test
public void testInster() throws Exception {
//3.编写sql语句,动态值的部分使用 ? 代替
String sql = "insert into t_user(account,password,nickname)values(?,?,?);";
executeUpdate(sql, "test", "1233", "小李");
}
@Test
public void testUpdate() throws Exception {
//3.编写sql语句,动态值的部分使用 ? 代替
String sql = "update t_user set nickname=? where i=?;";
executeUpdate(sql, "小化",3);
}
@Test
public void testDelte() throws Exception {
//3.编写sql语句,动态值的部分使用 ? 代替
String sql = "delete from t_user where id=?;";
executeUpdate(sql, 1);
}
}