JDBC

JDBCUtils

public class JDBCUtils {

    private static final String url = "jdbc:mysql://localhost:3306/test";
    private static final String user = "root";
    private static final String password = "3306";
    private static final String driverName = "com.mysql.jdbc.Driver";

    static{
        try{
            /*注册驱动，只需注册一次，不重复注册*/
            Class.forName(driverName);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        try {
            /*获取数据库连接对象*/
            Connection con = DriverManager.getConnection(url,user,password);
            return con;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public static void closeResource(Connection con, Statement stat,ResultSet rs){
        /*关闭资源*/
        if(con != null){
            try{
                con.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        if(stat != null){
            try{
                stat.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        if(rs != null){
            try{
                rs.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        try{
            /*建立连接*/
            Connection con = JDBCUtils.getConnection();
            /*书写sql语句*/
            String sql = "select * from students where name like ? ";
            /*得到执行sql对象：statement/preparedstatement
             * statement:执行静态sql语句，每次执行语句，数据库都要执行sql编译操作，效率低，有参数的时候还需要拼接字符串，不能防止sql注入
             * preparedstatement:预编译sql语句，执行效率高，防止sql注入
             */
            PreparedStatement pre = con.prepareStatement(sql);
            pre.setString(1, "王%");
            /*获得查询结果并返回，返回结果使用结果集*/
            ResultSet rs = pre.executeQuery();
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}

事物

public class TransactionTest {
    public static void main(String[] args) {
        Connection con = JDBCUtils.getConnection();
        PreparedStatement pre = null;
        Double money = 500d;
        try{
            /*降工资*/
            String sql1 = "update Salary set money = money-? where id = ?";
            pre = con.prepareStatement(sql1);
            pre.setDouble(1,money);
            pre.setString(2,"test1");
            pre.executeUpdate();

            /*涨工资*/
            String sql2 = "update Salary set money = money+? where id = ?";
            pre = con.prepareStatement(sql2);
            pre.setDouble(1,money);
            pre.setString(2,"test2");
            pre.executeUpdate();

            /*事物提交*/
            con.commit();
        }catch (Exception e){
            try {
                /*事物回滚*/
                con.rollback();
                System.out.println("工资修改失败！");
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,pre,null);
        }
    }
}