java_mysql_jdbc

• 注册驱动 ( jdbc > 5.1 可省略 )

Class.forName("com.mysql.jdbc.Driver");

• 获取连接

使用 DreiverManager获取连接对象

Connection conn = DriverManager.getConnection(url,user,pass);

• 获取执行 sql 对象

使用连接对象conn创建执行sql对象

Statement stmt = conn.createStatement();

• 执行sql

• 对 表 和 数据库 的增删改

• 对 表中数据 的增删改

stmt.executeUpdate(sql);

• 查询操作

ResultSet rs = stmt.executeQuery(sql);

sql Class

DriverManager

• 获取数据库连接
• 注册驱动

Connection

• 获取普通执行sql对象

Statement createStatement()

• 获取预编译执行sql对象 (防止sql注入)

PreparedStatement prepareStatement(sql)

• 事务管理
• 开启事务

conn.setAutoCommit(boolean autoCommit) //自动提交默认为 true，开启事务则为 false

• 提交事务

conn.commit()

• 回滚事务

conn.rollback()

Statement

• 执行sql语句

PreparedStatement

• 获取对象

PreparedStatement pstmt = conn.prepareStatement(sql)

• 设置参数

pstmt.setInt();

pstmt.setString(); …

• 执行sql]

pstmt.executeQuery() / executeUpdate()

数据库连接池 Druid

exp

package cn.noybzy.exp;

import cn.noybzy.pojo.User;
import org.junit.Test;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;

public class demo1 {
    private String url = "jdbc:mysql://IP:3306/demo?useSSL=false";
    private String name = "root";
    private String pass = "******";

    @Test
    public void add() throws Exception{

        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        LocalDateTime now = LocalDateTime.now();
        String nowDate = dateTimeFormatter.format(now);
        //插入对象
        User user = new User(17,"wuyao",33,"N","重庆",100,100,nowDate);

        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //获取连接
        Connection conn = DriverManager.getConnection(url,name,pass);

        //获取执行sql对象
        String sql = "insert into user1 values (?,?,?,?,?,?,?,?) ";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置参数
        pstmt.setInt(1,user.getId());
        pstmt.setString(2,user.getName());
        pstmt.setInt(3,user.getAge());
        pstmt.setString(4,user.getGender());
        pstmt.setString(5,user.getAddess());
        pstmt.setDouble(6,user.getMath());
        pstmt.setDouble(7,user.getEngish());
        pstmt.setString(8,user.getHire_date());

        int i = pstmt.executeUpdate();
        System.out.println(i);

        pstmt.close();
        conn.close();

    }

    @Test
    public void del() throws Exception{

        int uid = 17;

        //注册驱动

        //获取连接对象
        Connection conn = DriverManager.getConnection(url,name,pass);

        String sql = "delete from user1 where id = ?";
        //获取预编译sql执行对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        pstmt.setInt(1,uid);

        int i = pstmt.executeUpdate();
        System.out.println("删除" + i + "条数据");

        pstmt.close();
        conn.close();

    }

    @Test
    public void update() throws Exception{
        int uid = 17;

        //注册驱动

        //获取连接对象
        Connection conn = DriverManager.getConnection(url,name,pass);

        String sql = "update user1 set math = ?,english = ? where id = ?";
        //获取预编译sql执行对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setDouble(1,66.2);
        pstmt.setDouble(2,77.7);
        pstmt.setInt(3,uid);

        int i = pstmt.executeUpdate();
        System.out.println("修改" + i + "条数据");

        pstmt.close();
        conn.close();
    }

    @Test
    public void getDate() throws Exception{
        int x = 0;

        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //获取连接
        Connection connection = DriverManager.getConnection(url, name, pass);

        //创建对象
        Statement stmt = connection.createStatement();

        String sql = "select * from user1 order by  math desc limit "+ x * 5 +",5";

        //执行sql
        ResultSet rs = stmt.executeQuery(sql);

        ArrayList<User> users = new ArrayList<>();

        while(rs.next()){
            User u = new User();

            u.setId(rs.getInt("id"));
            u.setName(rs.getString("name"));
            u.setGender(rs.getString("gender"));
            u.setAddess(rs.getString("address"));
            u.setMath(rs.getDouble("math"));
            u.setEngish(rs.getDouble("english"));
            u.setHire_date(rs.getString("hire_date"));

            users.add(u);
        }

        System.out.println(users.size() + "-->:");

        for(User us:users){
            System.out.println(us);
        }

        stmt.close();
        connection.close();
    }

}