jdbc中考虑事务的通用增删改查

1.通用的增删改操作

   // 通用的增删改操作---version 2.0 （考虑上事务）
    public int update(Connection conn,String sql, Object... args) {// sql中占位符的个数与可变形参的长度相同！
        PreparedStatement ps = null;
        try {
            // 1.预编译sql语句，返回PreparedStatement的实例
            ps = conn.prepareStatement(sql);
            // 2.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);// 小心参数声明错误！！
            }
            // 3.执行
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 4.资源的关闭
            DBCUtil.close(null, ps,null);

        }
        return 0;
    }

测试：

   @Test
    public void testUpdate() {
        Connection conn = null;
        try {
            conn = DBCUtil.getConnection();
            System.out.println(conn.getAutoCommit());//true
            //1.取消数据的自动提交
            conn.setAutoCommit(false);

            String sql1 = "update user_table set balance = balance - 100 where user = ?";
            update(conn,sql1, "AA");

            //模拟网络异常
            System.out.println(10 / 0);

            String sql2 = "update user_table set balance = balance + 100 where user = ?";
            update(conn,sql2, "BB");

            System.out.println("转账成功");

            //2.提交数据
            conn.commit();

        } catch (Exception e) {
            e.printStackTrace();
            //3.回滚数据
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{

            DBCUtil.close(conn, null,null);
        }

    }

2.通用的查询，返回数据表中的一条记录：

    public <T> T query(Connection conn,Class<T> clazz,String sql,Object...args){

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1,args[i]);
            }

            rs = ps.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();

            int columnCount = rsmd.getColumnCount();

            if(rs.next()){

                T t = clazz.newInstance();

                for (int i = 0; i < columnCount; i++) {

                    Object columnValue = rs.getObject(i + 1);

                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }

                return t;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            DBCUtil.close(null,ps,rs);
        }
        return null;
    }

测试：

  @Test
    public void testQuery(){
        Connection conn = null;
        try {
            conn = DBCUtil.getConnection();

            conn.setAutoCommit(false);

            String sql = "select user,password,balance from user_table where user = ?";
            User user = query(conn, User.class, sql, "CC");

            System.out.println(user);

            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            if(conn != null){
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        } finally {
            DBCUtil.close(conn,null,null);
        }
    }

3.通用的查询，返回数据表中的多条记录：

public  <T> List<T> getForList(Connection conn,Class<T> clazz, String sql, Object...args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {

            ps = conn.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1,args[i]);
            }

            rs = ps.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();

            int columnCount = rsmd.getColumnCount();

            //创建集合对象
            ArrayList<T> list = new ArrayList<>();

            while(rs.next()){
                T t = clazz.newInstance();

                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);

                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBCUtil.close(null,ps,rs);
        }

        return null;
    }

测试：

 @Test
    public void testGetForList(){

        Connection conn = null;
        try {
            conn = DBCUtil.getConnection();

            conn.setAutoCommit(false);

            String sql = "select * from user_table";
            List<User> users = getForList(conn, User.class, sql);

            for (User user : users) {

                System.out.println(user);
            }


            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            if(conn != null){
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        } finally {
            DBCUtil.close(conn,null,null);
        }
    }

4.用于查询特殊值的通用的方法

	//用于查询特殊值的通用的方法
	public <E> E getValue(Connection conn,String sql,Object...args){
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			for(int i = 0;i < args.length;i++){
				ps.setObject(i + 1, args[i]);
				
			}
			
			rs = ps.executeQuery();
			if(rs.next()){
				return (E) rs.getObject(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(null, ps, rs);
			
		}
		return null;
		
	}

5.DBCUtil

package loey.DBCUtil;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * JDBC工具类，简化JDBC编程
 *
 */
public class DBCUtil {

    /**
     * 工具类中的构造方法是私有的
     * 因为工具类中的方法都是静态的，直接通过类名去调即可。
     */
    private DBCUtil() {

    }

    private static Properties getProperties(){

        //ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        //String driver = bundle.getString("driver");
        //String url = bundle.getString("url");
        //String user = bundle.getString("user");
        //String password = bundle.getString("password");
        //String sql = bundle.getString("sql");

        // 1.读取配置文件中的4个基本信息
        Properties pros = new Properties();

        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        try {
            pros.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }

        return pros;
    }

    /**
     * 静态代码块，类加载的时候执行
     * 把注册驱动程序的代码放在静态代码块中，避免多次获取连接对象时重复调用
     */
    static{
        Properties pros = getProperties();
        String driver = pros.getProperty("driver");
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * @return 获取连接
     * @throws SQLException
     */
    public static Connection getConnection() throws Exception {

        Properties pros = getProperties();

        String url = pros.getProperty("url");
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");

        Connection conn = DriverManager.getConnection(url, user, password);

        return conn;
    }

    public static void close(Connection conn, Statement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
user=root
password=1127