JDBC MySQL入门create，delete，insert，select演示

技术乐于分享，转载请说明出处。

一.JDBC编程步骤：
 
1.注册一个Driver 
2. 建立连接
3.获得一个Statement对象 
4.通过Statement执行sql语句
5.处理结果集 
6.关闭数据库连接

二.代码演示：
 
import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Scanner;

/*

 * 本此程序用于JDBC技术下MySQL数据库的基本演示

 * 程序展示了注册数据库驱动、加载驱动等MySQL的JDBC连接技术

 * 以及展示了如何创建表，执行简单查询、删除记录的简单操作

 * 运行本程序之前请确保已经导入驱动包，作者所用驱动包名称为：mysql-connector-java-5.1.24-bin.jar

 * 确保文中url：jdbc:mysql://localhost/school存在

 */

public class TestSql {

/**

 * @author 史明-杭州电子科技大学

 */

private String url = "jdbc:mysql://localhost/school";// 格式jdbc:mysql:（协议）localhost（IP地址）school（库名）

private String user = "root";//数据库用户名

private String password = "root";//数据库密码

private static String driver = "com.mysql.jdbc.Driver";//驱动名

private Connection connection = null;

private Statement statement = null;

private ResultSet resultSet = null;

public TestSql() {

}

/*

 * 虽然try{}代码块可以放在构造函数中， 但JAVA规范中明确规定：所有驱动程序必须在静态初始化代码块中将驱动注册到驱动程序管理器中

 */

static {

try {

Class.forName(driver);// 注册驱动

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

/*

 * 查询演示

 */

public void selectTest() {

String command = "select *from student";

try {

connection = DriverManager.getConnection(url, user, password);// 建立连接

statement = connection.createStatement();

resultSet = statement.executeQuery(command);

while (resultSet.next()) {

System.out.println(resultSet.getObject(1) + "\t"

+ resultSet.getObject(2));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

free(resultSet, statement, connection);

}

}

/*

 * 插入记录演示

 */

public void insertTest() {

String command = "INSERT INTO student" + "(name, grade)"

+ "VALUES('Jack', 99)";

try {

connection = DriverManager.getConnection(url, user, password);

statement = connection.createStatement();

statement.executeUpdate(command);

} catch (SQLException e) {

e.printStackTrace();

} finally {

free(resultSet, statement, connection);

}

}

/*

 * 创建表演示

 */

public void createTest() {

String command = "create table student"

+ "(name char(20) primary key, grade int )";

try {

connection = DriverManager.getConnection(url, user, password);

statement = connection.createStatement();

statement.executeUpdate(command);

} catch (SQLException e) {

System.out.println("相同表已存在");

} finally {

free(resultSet, statement, connection);

}

}

/*

 * 删除记录演示

 */

public void deleteTest() {

String command = "DELETE FROM student" + " where name = 'Jack' ";

try {

connection = DriverManager.getConnection(url, user, password);

statement = connection.createStatement();

statement.executeUpdate(command);

} catch (SQLException e) {

e.printStackTrace();

} finally {

free(resultSet, statement, connection);

}

}

/*

 * 关闭数据库资源 数据库资源稀缺，不要等回收器来回收，养成主动关闭数据库的习惯

 * 注意关闭顺序，依次ResultSet，Statement，Connection

 */

public void free(ResultSet resultSet, Statement statement,

Connection connection) {

try {

if (statement != null) {

statement.close();

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (resultSet != null) {

try {

resultSet.close();

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (connection != null) {

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

}

}

@SuppressWarnings("resource")

public static void main(String[] args) {

Scanner scanner = new Scanner(System.in);

TestSql testSql = new TestSql();

System.out.println("首次使用先创建表");

while (true) {

System.out.println("输入1-4，进行测试");

System.out.println("1.创建表");

System.out.println("2.插入记录");

System.out.println("3.删除记录");

System.out.println("4.查询记录");

int i = scanner.nextInt();

switch (i) {

case 1:

testSql.createTest();break;

case 2:

testSql.insertTest();break;

case 3:

testSql.deleteTest();break;

case 4:

testSql.selectTest();break;

}

}

}

}