jdbc学习笔记day04

基于preparedstatement的增删改查

查询

引入两个jar包到lib

创建名为JDBCOperation的Java对象

编写以下代码

package com.atguigu.base;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCOperation {

    @Test
    public void testQuerySingleRowAndCol() throws Exception{
        //单行单列 一个数据
        //注册驱动
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "1234");
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(*) as count FROM t_emp");
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            int count = resultSet.getInt("count");
            System.out.println(count);

        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testQuerySingleRow() throws Exception{
        //注册驱动
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "1234");

        //获取preparedstatement对象
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT emp_id,emp_name,emp_salary,emp_age FROM t_emp WHERE emp_id = ?");

        //为占位符赋值
        preparedStatement.setInt(1, 5);

        //执行
        ResultSet resultSet = preparedStatement.executeQuery();

        while(resultSet.next()){
            int emp_id = resultSet.getInt("emp_id");
            String emp_name = resultSet.getString("emp_name");
            double emp_salary = resultSet.getDouble("emp_salary");
            int emp_age = resultSet.getInt("emp_age");
            System.out.println(emp_id + "\t" + emp_name + "\t" + emp_salary + "\t" + emp_age);
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
    
       @Test
    public void testQueryMoreRow() throws Exception{
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "1234");
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT emp_id,emp_name,emp_salary,emp_age FROM t_emp WHERE emp_age > ?");
        preparedStatement.setInt(1, 25);
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            int emp_id = resultSet.getInt("emp_id");
            String emp_name = resultSet.getString("emp_name");
            double emp_salary = resultSet.getDouble("emp_salary");
            int emp_age = resultSet.getInt("emp_age");
            System.out.println(emp_id + "\t" + emp_name + "\t" + emp_salary + "\t" + emp_age);
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();

    }
}

• 注意事项ps:
  [@Test是Junit包里面的注释，表示测试]
  [遍历resultset的时候，至少执行一次result.next()]

---

插入

    @Test
    public void testInsert() throws Exception{
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "1234");
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO t_emp(emp_name,emp_salary,emp_age) VALUES(?,?,?)");
        preparedStatement.setString(1, "Rose");
        preparedStatement.setDouble(2, 345.67);
        preparedStatement.setInt(3, 28);
        //返回一个int
        int result = preparedStatement.executeUpdate();

        //根据受影响行数，做判断，得到成功或失败
        if(result > 0){
            System.out.println("成功");

        }else{
            System.out.println("失败");
        }

        preparedStatement.close();
        connection.close();
    }

---

修改

    @Test
    public void testUpdate() throws Exception{
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "1234");

        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE t_emp SET emp_salary = ? WHERE emp_id = ?");

        preparedStatement.setDouble(1, 888.88);
        preparedStatement.setInt(2, 6);

        int result = preparedStatement.executeUpdate();

        if(result > 0){
            System.out.println("成功");
        }else{
            System.out.println("失败");
        }

        preparedStatement.close();
        connection.close();
    }

删除

    @Test
    public void testDelete() throws Exception{
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "1234");

        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM t_emp WHERE emp_id = ?");

        preparedStatement.setInt(1, 6);

        int result = preparedStatement.executeUpdate();

        if(result > 0){
            System.out.println("成功");
        }else{
            System.out.println("失败");
        }

        preparedStatement.close();
        connection.close();
    }

需要注意的问题：
用完一定资源释放