JavaWeb5.1【JDBC：基本概念、快速入门】
1. 概念
Java DataBase Connectivity Java 数据库连接， Java语言操作数据库
JDBC本质：其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。
各个数据库厂商去实现这套接口，提供数据库驱动jar包。
我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。
2. 快速入门
步骤
1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
1.复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下
(在模块下新建libs文件夹，与src同级)(建目录方便管理，建不建都行，名称任意)
2.在jar包/libs目录上右键-->Add As Library
2. 注册驱动
3. 获取数据库连接对象 Connection
4. 定义sql语句
5. 获取执行sql语句的对象 Statement
6. 执行sql，接收返回结果
7. 处理结果
8. 释放资源
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
1 package com.yub4by.jdbc; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.Statement; 6 94 public class JDBCDemo1 { 95 public static void main(String[] args) throws Exception { 96 //1 导入驱动jar包 97 98 //2 注册驱动 99 Class.forName("com.mysql.jdbc.Driver"); 100 101 //3 获取数据库连接对象 102 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hm_db2", "root", "root"); 103 // Connection conn = DriverManager.getConnection("jdbc:mysql:///hm_db2", "root", "root"); 104 105 //4 定义sql语句 106 String sql = "update account set balance=500 where id=1"; 107 108 //5 获取执行sql语句的对象 109 Statement stmt = conn.createStatement(); 110 111 //6 执行sql语句，接收返回结果 112 int count = stmt.executeUpdate(sql); 113 114 //7 处理结果 115 System.out.println(count); //1 116 117 //8 释放资源 118 stmt.close(); 119 conn.close(); 120 121 } 122 }
优化写法-例子
1 package com.yub4by.jdbc; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.SQLException; 6 import java.sql.Statement; 7 8 /** 9 * 练习： 10 * 1. account表 添加一条记录 11 * 2. account表 修改记录 12 * 3. account表 删除一条记录 13 */ 14 //添加一条记录 15 public class JDBCDemo2 { 16 public static void main(String[] args) { 17 18 19 Connection conn = null; 20 Statement stmt = null; 21 try { 22 //1注册驱动 23 Class.forName("com.mysql.jdbc.Driver"); 24 25 //2获取数据库连接对象Connection 26 // conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hm_db2", "root", "root"); 27 conn = DriverManager.getConnection("jdbc:mysql:///hm_db2", "root", "root"); 28 29 //3获取执行sql语句的对象Statement 30 stmt = conn.createStatement(); 31 32 //4定义sql语句 33 String sql = "insert into account values(null, '王五', 3000)"; 34 35 //5执行sql语句 36 int count = stmt.executeUpdate(sql); //返回影响数据库的行数 37 38 //6处理结果 39 System.out.println(count); 40 if(count > 0){ 41 System.out.println("添加成功"); 42 }else { 43 System.out.println("添加失败"); 44 } 45 } catch (ClassNotFoundException | SQLException e) { 46 e.printStackTrace(); 47 }finally { 48 //7释放资源 49 if(stmt != null){ //避免空指针异常 50 try { 51 stmt.close(); 52 } catch (SQLException e) { 53 e.printStackTrace(); 54 } 55 } 56 if(conn != null){ 57 try { 58 conn.close(); 59 } catch (SQLException e) { 60 e.printStackTrace(); 61 } 62 } 63 } 64 65 66 } 67 }
1 package com.yub4by.jdbc; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.SQLException; 6 import java.sql.Statement; 7 8 /** 9 * 练习： 10 * 1. account表 添加一条记录 11 * 2. account表 修改记录 12 * 3. account表 删除一条记录 13 */ 14 //修改记录 15 public class JDBCDemo3 { 16 public static void main(String[] args) { 17 18 Connection conn = null; 19 Statement stmt = null; 20 try { 21 Class.forName("com.mysql.jdbc.Driver"); 22 conn = DriverManager.getConnection("jdbc:mysql:///hm_db2", "root", "root"); 23 stmt = conn.createStatement(); 24 25 String sql = "update account set balance=1500 where id=3"; 26 int count = stmt.executeUpdate(sql); 27 System.out.println(count); 28 if(count != 0){ 29 System.out.println("修改成功"); 30 }else { 31 System.out.println("修改失败"); 32 } 33 } catch (ClassNotFoundException | SQLException e) { 34 e.printStackTrace(); 35 }finally { 36 if(stmt != null){ 37 try { 38 stmt.close(); 39 } catch (SQLException e) { 40 e.printStackTrace(); 41 } 42 } 43 if(conn != null){ 44 try { 45 conn.close(); 46 } catch (SQLException e) { 47 e.printStackTrace(); 48 } 49 } 50 } 51 52 } 53 }
1 package com.yub4by.jdbc; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.SQLException; 6 import java.sql.Statement; 7 8 /** 9 * 练习： 10 * 1. account表 添加一条记录 11 * 2. account表 修改记录 12 * 3. account表 删除一条记录 13 */ 14 //删除一条记录 15 public class JDBCDemo4 { 16 public static void main(String[] args) { 17 18 Connection conn = null; 19 Statement stmt = null; 20 try { 21 Class.forName("com.mysql.jdbc.Driver"); 22 conn = DriverManager.getConnection("jdbc:mysql:///hm_db2", "root", "root"); 23 stmt = conn.createStatement(); 24 25 String sql = "delete from account where id=3"; 26 int count = stmt.executeUpdate(sql); 27 System.out.println(count); 28 if(count != 0){ 29 System.out.println("删除成功"); 30 }else { 31 System.out.println("删除失败"); 32 } 33 } catch (ClassNotFoundException | SQLException e) { 34 e.printStackTrace(); 35 }finally { 36 if(stmt != null){ 37 try { 38 stmt.close(); 39 } catch (SQLException e) { 40 e.printStackTrace(); 41 } 42 } 43 if(conn != null){ 44 try { 45 conn.close(); 46 } catch (SQLException e) { 47 e.printStackTrace(); 48 } 49 } 50 } 51 52 } 53 }
1 package com.yub4by.jdbc; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.SQLException; 6 import java.sql.Statement; 7 8 //创建表 9 public class JDBCDemo5 { 10 public static void main(String[] args) { 11 12 Connection conn = null; 13 Statement stmt = null; 14 try { 15 Class.forName("com.mysql.jdbc.Driver"); 16 conn = DriverManager.getConnection("jdbc:mysql:///hm_db2", "root", "root"); 17 stmt = conn.createStatement(); 18 19 String sql = "create table student(id int, name varchar(20))"; 20 int count = stmt.executeUpdate(sql); 21 System.out.println(count); //0 22 } catch (ClassNotFoundException | SQLException e) { 23 e.printStackTrace(); 24 }finally { 25 if(stmt != null){ 26 try { 27 stmt.close(); 28 } catch (SQLException e) { 29 e.printStackTrace(); 30 } 31 } 32 if(conn != null){ 33 try { 34 conn.close(); 35 } catch (SQLException e) { 36 e.printStackTrace(); 37 } 38 } 39 } 40 41 } 42 }
