P13 preparedstatement实现表数据的增删改操作

preparedstatement实现表数据的添加操作

/*
使用preparedstatement来替换statement，实现对数据表的增删改查操作

增删改：查
 */
public class PreparedStatementUpdate {

    //向customers表中添加一条记录
    @Test
    public void testInsert() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.读取配置文件中的4个基本信息
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

            Properties pros = new Properties();

            pros.load(is);

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

            //2.加载驱动
            Class.forName(driverClass);

            //3.获取连接
            conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);

            //4.预编译sql语句，返回PreparedStatement的实例
            String sql="insert into customers(name,email,birth)values(?,?,?)";//?:占位符
            ps = conn.prepareStatement(sql);
            //5.填充占位符 索引数据库从1开始
            ps.setString(1,"哪吒");
            ps.setString(2,"nezha@126.com");

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//日期格式化
            java.util.Date date = sdf.parse("1000-01-01");
            ps.setDate(3, new Date(date.getTime()));

            //6.执行sql
            ps.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        } finally {
            //7.资源的关闭
            try {
                if(ps!=null) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn!=null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

封装数据库连接和关闭操作

util包下的JDBCUtil类

/*
操作数据库的工具类
 */
public class JDBCUtil {
    /*
    获取数据库的连接
     */
    public static Connection getConnection() throws Exception {
        //1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();

        pros.load(is);

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

        //2.加载驱动
        Class.forName(driverClass);

        //3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
    //关闭连接和statement的操作
    public static void closeResource(Connection conn, PreparedStatement ps){
        //7.资源的关闭
        try {
            if(ps!=null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

preparedstatement修改表中的一条记录

//修改customers表中的一条记录
    @Test
    public  void testUpdate()  {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库的连接
            conn = JDBCUtil.getConnection();

            //2.预编译sql语句，返回preparedstatement实例
            String sql="update customers set name=? where id=?";
            ps = conn.prepareStatement(sql);

            //3.填充占位符
            ps.setString(1, "莫扎特");
            ps.setInt(2, 18);

            //4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.资源关闭
            JDBCUtil.closeResource(conn, ps);
        }

    }

通用的增删改操作的方法

//通用的增删改操作的方法
    public void update(String sql,Object ...args)  {//占位符的个数与可变形参的长度一致
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库的连接
            conn = JDBCUtil.getConnection();
            //2.预编译sql语句，返回preparedstatement实例
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1, args[i]);//注意索引
            }
            //4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            JDBCUtil.closeResource(conn, ps);
        }
    }

测试通用的增删改操作的方法

@Test
    public void testCommonUpdate(){
//        String sql="delete from customers where id=?";
//        update(sql, 3);
        String sql="update  `order` set order_name=? where order_id=?";
        update(sql, "DD","2");
    }