jdbc操作mysql

首先在mysql可视化工具中创建表，添加初始数据（可不加）。

在idea创建新模块，添加相关依赖。

<dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>

创建与数据库表对应的实体类。

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
    int eid;
    String ename;
    int age;
    int sex;
}

在resource中创建数据库属性文件 jdbc.properties。记得url中要加上时区。

jdbc.url=jdbc:mysql://localhost:3306/javase?useUnicode=true&useSSL=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.username=root
jdbc.password=123456

创建工具类，获取连接，注册驱动。

public class ConnectionInstance {
    public static Connection getConn() throws IOException, SQLException, ClassNotFoundException {
        //声明配置文件位置
        File file = new File("src/main/resources/dataSource.properties");
        FileInputStream fis = new FileInputStream(file);
        //新建properties加载文件
        Properties properties = new Properties();
        properties.load(fis);
        //通过properties创建连接
        String url = properties.getProperty("jdbc.url");
        String username = properties.getProperty("jdbc.username");
        String password = properties.getProperty("jdbc.password");
        String driver = properties.getProperty("jdbc.driver");
        //注册驱动
        Class.forName(driver);
        //获取连接
        Connection connection = DriverManager.getConnection(url, username, password);
        //返回连接
        return connection;
    }
}

创建测试类，进行测试。

/**
 * jdbc连接数据库查询
 */
public class JdbcTest {
    /**
     * 单个查询
     * @throws SQLException
     * @throws IOException
     * @throws ClassNotFoundException
     */
    @Test
    public void test1() throws SQLException, IOException, ClassNotFoundException {
        Connection conn = ConnectionInstance.getConn();
        String sql="select * from emp where eid=?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setInt(1,3);
        //执行查询，返回一个结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        Emp emp = new Emp();
        while (resultSet.next()){
            int anInt = resultSet.getInt(1);
            emp.setEid(anInt);
            String ename = resultSet.getString(2);
            emp.setEname(ename);
            int age=resultSet.getInt(3);
            emp.setAge(age);
            int sex = resultSet.getInt(4);
            emp.setSex(sex);
        }
        System.out.println(emp);
        if (resultSet!=null){
            resultSet.close();
        }
        if (preparedStatement!=null){
            preparedStatement.close();
        }
        if (conn!=null){
            conn.close();
        }
    }

    /**
     * 插入数据
     * @throws SQLException
     * @throws IOException
     * @throws ClassNotFoundException
     */
    @Test
    public void test2() throws SQLException, IOException, ClassNotFoundException {
        Connection conn = ConnectionInstance.getConn();
        String sql="insert into emp values(?,?,?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setInt(1,6);
        preparedStatement.setString(2,"xw");
        preparedStatement.setInt(3,25);
        preparedStatement.setInt(4,1);
        preparedStatement.execute();
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 批量查询
     * @throws SQLException
     * @throws IOException
     * @throws ClassNotFoundException
     */
    @Test
    public void test3() throws SQLException, IOException, ClassNotFoundException {
        Connection conn = ConnectionInstance.getConn();
        String sql="select * from emp";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        ArrayList<Emp> list = new ArrayList<>();
        while(resultSet.next()){
            Emp emp = new Emp();
            int eid = resultSet.getInt(1);
            emp.setEid(eid);
            String ename = resultSet.getString(2);
            emp.setEname(ename);
            int age = resultSet.getInt(3);
            emp.setAge(age);
            int sex = resultSet.getInt(4);
            emp.setSex(sex);
            list.add(emp);
        }
        for (Emp emp:list) {
            System.out.println(emp);
        }
        if (ps != null) {
            ps.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}