Java - 数据库连接驱动JDBC详解（一）

• • • JDBC
    • 如何使用JDBC
    • Statement SQL执行对象

• Java Database Connectivity: Java数据库连接, 是Sun公司提供的一套编程接口,用于通过Java语言和数据库进行连接.
• 为什么使用JDBC: Java语言有可能会连接多种数据库,如果没有JDBC,Java程序员需要每一种数据库都学习一套独立的方法, Sun公司定义了JDBC接口后,各个数据库厂商根据此接口写各自的实现类(驱动), 对于Java程序员而言只需要掌握JDBC接口中的方法调用即可, 通过一套方法可以操作多种数据库.

1. 创建Maven工程
2. 在pom文件中添加以下内容

 
  	
  	
  		mysql
  		mysql-connector-java
  		5.1.6
  	
  

3. 创建Demo01.java类文件 在main方法中添加以下代码

//1. 注册驱动  运行提示ClassNotFound 把这行代码注释掉
Class.forName("com.mysql.jdbc.Driver");
//2. 获取连接对象
Connection conn = 
	DriverManager.getConnection(
		"jdbc:mysql://localhost:3306/newdb3", 
		"root", "");
System.out.println(conn);
//3. 创建SQL语句执行对象
Statement s = conn.createStatement();
//4. 执行SQL
String sql = "create table jdbct1"
	+ "(id int,name varchar(10),age int)";
s.execute(sql);
System.out.println("执行完成!");
//5. 关闭资源
conn.close();

• execute(sql): 可以执行任意SQL语句,但是推荐执行DDL(数据定义语言:create drop alter)
• executeUpdate(sql): 执行增删改相关的sql
• executeQuery(sql): 执行查询相关的sql

public class TestJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/newdb3",
                "root",
                ""
        );
        System.out.println(conn);
        Statement s = conn.createStatement();
        String sql = "create table jdbct2(id int, name varchar(10))";
        s.execute(sql);
        System.out.println("执行完成");
        conn.close();
    }
}

package jdbcDemo;

import org.junit.Test;

import java.sql.*;

public class TestJDBC2 {
    @Test
    public void insert () throws ClassNotFoundException, SQLException {
        System.out.println("insert");
        Class.forName("com.mysql.jdbc.Driver");
         Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/newdb3",
                "root",
                ""
        );
        Statement s = conn.createStatement();
        String sql = "insert into emp(empno,ename) values(101, 'Tom')";
        s.executeUpdate(sql);
        System.out.println("insert 执行完成");
        conn.close();
    }

    @Test
    public void delete () throws ClassNotFoundException, SQLException {
        System.out.println("delete");
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/newdb3",
                "root",
                ""
        );
        Statement s = conn.createStatement();
        String sql = "delete from emp where empno = 101";
        s.executeUpdate(sql);
        System.out.println("delete 执行完成");
        conn.close();
    }

    @Test
    public void update () throws ClassNotFoundException, SQLException {
        System.out.println("update");
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/newdb3",
                "root",
                ""
        );
        Statement s = conn.createStatement();
        String sql = "update emp set ename = 'Dave' where empno = 101";
        s.executeUpdate(sql);
        System.out.println("update 执行完成");
        conn.close();
    }

    @Test
    public void select () throws ClassNotFoundException, SQLException {
        System.out.println("select");
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/newdb3",
                "root",
                ""
        );
        Statement s = conn.createStatement();
        String sql = "select * from emp";
        ResultSet res = s.executeQuery(sql);
        while (res.next()) {
            String name = res.getString("ename");
            double sal = res.getDouble("sal");
            System.out.println(name + " : " + sal );
        }
        conn.close();
    }
}