【JDBC】PreparedStatement操作数据库

 建个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();
    }
}