本文共 8842 字，大约阅读时间需要 29 分钟。

使用PreparedStatement完成增删改查

ClassLoader.getSystemClassLoader()

使用系统的构造器

package com.nenu.ps.crud;import com.nenu.connection.ConnectionTest;import org.junit.Test;import java.io.IOException;import java.io.InputStream;import java.sql.*;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.Date;import java.util.Properties;public class PreparedStatementUpdateTest {    //向数据表中添加数据    @Test    public void testInsert() throws Exception {        Connection conn = null;        PreparedStatement ps = null;        try {            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.预编译            String sql = "insert into customers(name,email,birth)values(?,?,?)";//        5.?占位符            ps = conn.prepareStatement(sql);            ps.setString(1, "哪吒");            ps.setString(2, "nezha@gmail.com");            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");            java.util.Date date = sdf.parse("1000-01-01");            Timestamp t = new Timestamp(date.getTime());            ps.setTimestamp(3, t);            //6.执行sql            ps.execute();        } catch (IOException e) {            e.printStackTrace();        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException throwables) {            throwables.printStackTrace();        } catch (ParseException e) {            e.printStackTrace();        } finally {            //7.资源的关闭            try {                ps.close();            } catch (SQLException throwables) {                throwables.printStackTrace();            }            try {                conn.close();            } catch (SQLException throwables) {                throwables.printStackTrace();            }        }    }}

使用了ps能有效避免sql注入

package com.nenu.util;import java.io.InputStream;import java.sql.*;import java.util.Properties;public class JDBCutil {    public static Connection getConnection() throws Exception {        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);        System.out.println(conn);        return conn;    }    public static void closeResourese(Connection conn, PreparedStatement ps) {        try {            if (ps!=null) {                ps.close();            }        } catch (SQLException throwables) {            throwables.printStackTrace();        }        try {            if (conn!=null) {                conn.close();            }        } catch (SQLException throwables) {            throwables.printStackTrace();        }    }    public static void closeResourese(Connection conn, PreparedStatement ps , ResultSet resultSet) throws SQLException {        try {            if (resultSet!=null) {                resultSet.close();            }        } catch (SQLException throwables) {            throwables.printStackTrace();        }        try {            if (ps!=null) {                ps.close();            }        } catch (SQLException throwables) {            throwables.printStackTrace();        }        try {            if (conn!=null) {                conn.close();            }        } catch (SQLException throwables) {            throwables.printStackTrace();        }    }}

我们将ps获取连接及关闭专门写到一个类中，因为我只要想使用JDBC都会用到这个类

package com.nenu.bean;import java.sql.Date;//ORM变成思想/*  一个数据对应一个java类  表中的一条记录对应java类的一个对象  表中的一个字段对应java类的属性 */public class Customer {    private int id;    private String name;    private String email;    private Date birth;    public Customer(int id, String name, String email, Date birth) {        this.id = id;        this.name = name;        this.email = email;        this.birth = birth;    }    @Override    public String toString() {        return "Customer{" +                "id=" + id +                ", name='" + name + '\'' +                ", email='" + email + '\'' +                ", birth=" + birth +                '}';    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getEmail() {        return email;    }    public void setEmail(String email) {        this.email = email;    }    public Date getBirth() {        return birth;    }    public void setBirth(Date birth) {        this.birth = birth;    }}

再把我们想更改数据的的一些信息封装起来

@Test    public void testUpdate() throws Exception {//        获取数据库连接        Connection conn = null;        PreparedStatement ps = null;        try {            conn = JDBCutil.getConnection();//        预编译sql语句，返回PreparedStatement的实例            String sql="update customers set name =? where id=?";            ps = conn.prepareStatement(sql);//        填充占位符            ps.setObject(1,"莫扎特");            ps.setObject(2,18);//        执行            ps.execute();        } catch (Exception e) {            e.printStackTrace();        } finally {//        资源的关闭            try {                JDBCutil.closeResourese(conn, ps);            } catch (Exception e) {                e.printStackTrace();            }        }

但是这种方法我们还是无法做到增删改通用一个代码

@Test    public static void update(String sql, Object... args) throws Exception {//sql当中占位符的个数应该与可变形参长度相同        Connection conn = null;        PreparedStatement ps = null;        try {            conn = JDBCutil.getConnection();            ps = conn.prepareStatement(sql);            for (int i = 0; i 

因为我们不知道查询的东西是什么属性所以统一使用setObject,…args里面会与占位符长度相同，这样就能在里面填充。不过调用update方法的时候不能在idea使用Test，直接使用main方法就好。

ORM编程思想

在这里我们引入一个编程思想

*/

package com.nenu.ps.crud;import com.nenu.bean.Customer;import com.nenu.util.JDBCutil;import org.junit.Test;import java.sql.*;public class CustomerForQuery {    @Test    public void testQuery1() throws Exception {        Connection conn = null;        PreparedStatement ps = null;        ResultSet resultSet = null;        try {            conn = JDBCutil.getConnection();            String sql = "select id,name,email,birth from customers where id=?";            ps = conn.prepareStatement(sql);            ps.setObject(1,1);//        执行，并返回结果集            resultSet = ps.executeQuery();//        处理结果集            if(resultSet.next()){                //判断结果集下一条是否有数据，像hashnext与next一半的功能                int id=resultSet.getInt(1);                String name=resultSet.getString(2);                String email =resultSet.getString(3);                Date birth = resultSet.getDate(4);    //            Object[] data =new Object[]{id,name,email,birth};                Customer customer = new Customer(id,name,email,birth);                System.out.println(customer);            }        } catch (Exception e) {            e.printStackTrace();        } finally {            try {                JDBCutil.closeResourese(conn,ps,resultSet);            } catch (SQLException throwables) {                throwables.printStackTrace();            }        }    }}

我们用ResultSet类将结果集拿出来，在customer中进行了无参构造，有参构造还有封装还重写了toString方法。

import com.nenu.bean.Order;import com.nenu.util.JDBCutil;import org.junit.Test;import java.lang.reflect.Field;import java.sql.*;public class OrderQuery {    @Test//    通用    public static Order orderForQuery(String sql, Object... args)  {        Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        try {            conn = JDBCutil.getConnection();            ps = conn.prepareStatement(sql);            for (int i = 0; i 

因为我们这次用的是order表，但是里面的列名却和我们封装类里面的的属性不一样，这时候就查找不到该数据库里面的列名，所以我们用了别名查找。

转载地址：https://blog.csdn.net/qq_39814938/article/details/106551613 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！