JDBC——Java数据库连接

---

一、概念

 

---

二、连接

1.得到 Connection 对象

先进行基本配置，然后我们可以简单的输出所有库名

①.DataSource 的方式

        //a)通过 DataSource 的方式得到 Connection 对象
        // show databases：显示所有的库
        // 类似：保存联系人信息
        MysqlDataSource db = new MysqlDataSource();
        db.setServerName("localhost");
        db.setPort(3306);
        db.setUser("abc");
        db.setPassword("123");
        db.setDatabaseName("base");
        db.setUseSSL(false);
        db.setCharacterEncoding("utf-8");
        //连接客户端的时区
        db.setServerTimezone("Asia/Shanghai");
        
       try (Connection c = db.getConnection()) {
            // Statement: 语句 ：要说的话
            try (PreparedStatement ps = c.prepareStatement("show databases")) {
                // execute: 执行   query：查询
                //
                try (ResultSet rs = ps.executeQuery()) {
                    // 遍历得到的结果，打印到控制台（屏幕）
                    while (rs.next()) {
                        String name = rs.getString(1);
                        System.out.println(name);
                    }
                }
            }
        }
        
// 上面try (Connection c = db.getConnection()) 的简写

//        Connection c = null;
//        try {
//            c = db.getConnection();
//        } finally {
//            if (c != null) {
//                c.close();
//            }
//        }
//
//

②. DriverManager 的方式

/**
 *
 * b)通过 DriverManager 的方式得到 Connection 对象
 */
public class Demo2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //通过反射将这个类加载
        Class.forName("com.mysql.jdbc.Driver");
        //连接JDBC下的协议号，是固定的+服务器地址
        String url = "jdbc:mysql://localhost:3306?characterEncoding=utf-8&serverTimezone/Shanghai";
        Connection c = DriverManager.getConnection(url,"abc","123");
        //目标就是为了得到一个Connection的对象
    }
}

2.得到语句对象

   String sql = "...";
   try (PreparedStatement ps = c.prepareStatement(sql)){
   }

3.带结果的 SQL 语句：select …、show …

   try (ResultSet rs = ps.executeQuery()) {
       // 遍历得到的结果，打印到控制台（屏幕）
        while (rs.next()) {
           String name = rs.getString(1);
           System.out.println(name);
        }
    }

4.不带结果的 SQL 语句

    try (Connection c = db.getConnection()) {
           // Statement: 语句 ：要说的话
           String sql = "create database base charset utf8mb4";
           try (PreparedStatement ps = c.prepareStatement(sql)) {
               int n = ps.executeUpdate();
               System.out.println(n);
           }
    }

4.1练习

try (Connection c = db.getConnection()) {
        String sql = "insert into books(name,count,total) values ('月亮与六便士',10,10)";
        String sql = "insert into books(name,count,total) values ('知更鸟女孩',12,31),('天才与白痴',32,44)";
        String sql = "insert into readers (name) values ('RM')";
        String sql = "insert into readers (name) values ('V'),('J-HOPE')";
        String sql = "update books set count = count -1 where bid = 1";
        String sql = "insert into records (rid,bid) values (2,1),(3,3)";  
        String sql = "delete from records where rid = 3 ";
        try (PreparedStatement ps = c.prepareStatement(sql)) {
            int n = ps.executeUpdate();
            System.out.println(n);
        }

 try (Connection c = db.getConnection()) {
       String sql = "select reid, rid, bid, borrowed_at from records order by reid";
       String sql = "delete from records where rid = 3 ";
       try (PreparedStatement ps = c.prepareStatement(sql)) {
            try (ResultSet rs = ps.executeQuery()) {
                //有几行，就执行几遍
                while (rs.next()) {
                    int reid = rs.getInt(1);
                    int rid = rs.getInt("rid");
                    String bid = rs.getString(3);
                    String borrowedAt = rs.getString("borrowed_at");
                    System.out.printf("%d, %d, %s, %s\n", reid, rid, bid, borrowedAt);
                }
            }
       }
 }

5.动态SQL语句

   String sql = "select reid, rid, bid, borrowed_at from records where reid > ? order by reid";
      try (PreparedStatement ps = c.prepareStatement(sql)) {
           // 有了 PreparedStatment之后，在execute 之前
           // 为占位符绑定真实的数据
           //1表示第一个占位符，可以有多个
           //可以根据不同类型setString/serInt,也就是n的类型
           ps.setString(1, n);
           System.out.println(ps);
            try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        int reid = rs.getInt(1);
                        int rid = rs.getInt("rid");
                        String bid = rs.getString(3);
                        String borrowedAt = rs.getString("borrowed_at");

                        System.out.printf("%d, %d, %s, %s\n", reid, rid, bid, borrowedAt);
                    }
             }
      }

6.直接输出自增id

    Scanner scanner = new Scanner(System.in);
    String name = scanner.nextLine();
    try (Connection c = db.getConnection()) {
    
        String sql = "insert into readers (name) values (?)";
        try (PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
             ps.setString(1, name);
             ((PreparedStatement) ps).executeUpdate();
             try (ResultSet rs = ps.getGeneratedKeys()) {
             
                 if (rs.next()) {
                     int rid = rs.getInt(1);
                     System.out.println(rid + "|" + name);
                 }
             }
        }
    }