【JDBC】PreparedStatement操作数据库

比眉伴天荒 2021-09-24 14:12 486阅读 0赞

建个user表测试

DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `name` varchar(255) DEFAULT NULL,
      `password` varchar(255) DEFAULT NULL,
      `id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

相关操作

package xyz.hashdog.jdbc;
    
    import org.junit.Before;
    import org.junit.Test;
    
    import java.sql.*;
    
    /**
     * 使用PreparedStatement来做数据库的操作
     */
    public class PreparedStatementTest {
    
        Connection connection;
        @Before
        public void conn() throws ClassNotFoundException, SQLException {
            //1.提供基本信息
            String url = "jdbc:mysql://localhost:3306/st-jdbc";
            String user = "root";
            String password = "hasodog2019";
    
            //2.获取Driver实现类
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            connection = DriverManager.getConnection(url, user, password);
            System.out.println(connection);
    
        }
        /**
         * 向user表添加一条记录
         */
        @Test
        public void insert() throws SQLException {
            //1.获得连接
            System.out.println(connection);
            //2.预编译sql,返回PreparedStatement的实例
            String sql = "insert into user(name,password)value(?,?)";
            PreparedStatement ps = connection.prepareStatement(sql);
            //3.填充
            ps.setString(1,"小米");
            ps.setString(2,"xiaomi");
            //4.执行
            ps.execute();
            //5.关闭资源
            ps.close();
            connection.close();
        }
    
    
        /**
         * 修改一条数据
         */
        @Test
        public void update() throws SQLException {
            //1.获得连接
            System.out.println(connection);
            connection.setAutoCommit(false);
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            //2.预编译sql,返回PreparedStatement的实例
            String sql = "update user set name=?,password=? where id=?";
            PreparedStatement ps = connection.prepareStatement(sql);
            //3.填充
            ps.setString(1,"大米");
            ps.setString(2,"dami");
            ps.setInt(3,1);
            //4.执行
            int i = ps.executeUpdate();
            System.out.println(i);
            connection.commit();
           // connection.rollback();
            //5.关闭资源
            ps.close();
            connection.close();
        }
    
    
        @Test
        public void select() throws SQLException {
            //1.获得连接
            System.out.println(connection);
            //2.预编译sql,返回PreparedStatement的实例
            String sql = "select * from user where id>=?";
            PreparedStatement ps = connection.prepareStatement(sql);
            //3.填充
            ps.setInt(1,1);
            //4.执行,返回结果集
            ResultSet resultSet = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //获得结果集字段数
            int columnCount = metaData.getColumnCount();
            System.out.println(columnCount);
            //返回true,证明有数据,指针下移
            while (resultSet.next()){
                //通过结果集元数据,遍历获取各字段值
                for (int i = 0; i < columnCount; i++) {
                    Object object = resultSet.getObject(i + 1);
                }
                //单个字段h获取,获取当前指针行的各个字段
                String name = resultSet.getString(1);
                String password = resultSet.getString(2);
                Integer id = resultSet.getInt(3);
            }
            //5.关闭资源
            ps.close();
            connection.close();
        }
    }