- 相关知识点
- JDBC是基于Java的数据库访问接口。Java应用程序中通过JDBC访问MySQL数据库需要以下步骤:
(1) 导入JDBC包;
(2) 注册JDBC驱动程序;
(3) 指定目标数据库,包括主机名、端口号、数据 库用户名、密码等;
// 设定JDBC驱动
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
//设定本地数据库的URL,并指定编码方式为UTF-8且关闭SSL连接
static final String DB_URL = "jdbc:mysql:///?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
(4) 创建连接对象,建立与数据库的连接;
//数据库连接对象
Connection connection = null;
// 注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
// 创建于指定数据库的连接
connection = DriverManager.getConnection(DB_URL, USER, PASS);
} catch (SQLException e) {
e.printStackTrace();
}
(5) 创建执行SQL语句的对象实例Statement;
//指定数据库操作的动作
String sqlScript = "你的具体sql操作语句,如select * from table where id = 0";
//生成查询对象
Statement stmt = (Statement) connection.createStatement();
//执行数据库操作
stmt.executeUpdate(sqlScript);
(6) 编写SQL语句;
mysql基本语法参见:https://blog.csdn.net/wangqianqianya/article/details/86997778
(7) 执行SQL语句。
//指定数据库操作的动作
String sqlScript = "你的具体sql操作语句,如select * from table where id = 0";
//执行数据库操作
stmt.executeUpdate(sqlScript);
- 数据库连接与数据库实例创建
package step1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MySQLWithJDBC {
//指定数据库驱动
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
//设定本地数据库的URL,并指定编码方式为UTF-8且关闭SSL连接
static final String DB_URL = "jdbc:mysql:///?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
// 创建数据库实例
public void createDatabaseInstance(Connection connection, String databaseName){
Statement stmt = null;
//在此处补全创建数据库实例的SQL语句
/******* Begin ******/
String sqlScript = "";
sqlScript="create database "+databaseName;
/******* End ******/
try {
//创建数据库查询对象
stmt = (Statement) connection.createStatement();
//执行查询
stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 建立与指定数据库的连接,并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
// 注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
// 创建于指定数据库的连接
connection = DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//删除数据库
public void dropDatabase(Connection connection, String databaseName){
Statement stmt = null;
String sqlScript = "drop database if exists " + databaseName;
try {
stmt = (Statement) connection.createStatement();
stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 获取数据库中已经存在的数据库实例
public ResultSet getExistDB(Connection connection, String databaseName) throws SQLException
{
ResultSet resultSet = null;
Statement stmt = null;
String sqlScript = "SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME=\"" + databaseName + "\"";
try {
stmt = (Statement) connection.createStatement();
resultSet = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return resultSet;
}
}
测试:
package step1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class MySQLWithJDBCTest {
public static void main(String[] args) throws SQLException {
MySQLWithJDBC mysql = new MySQLWithJDBC();
String databaseName = "";
//从命令行接收输入获得数据库的名字
Scanner sc = new Scanner(System.in);
databaseName = sc.next();
//建立数据库连接
Connection connection = mysql.getConnection(mysql.JDBC_DRIVER, mysql.DB_URL, mysql.USER, mysql.PASS);
mysql.dropDatabase(connection,databaseName);
//执行创建数据库的命令
mysql.createDatabaseInstance(connection, databaseName);
//查询数据库验证是否成功创建
ResultSet result = mysql.getExistDB(connection, databaseName);
//获取查询结果
while(result.next())
{
String dbName = result.getString("SCHEMA_NAME");
System.out.print("databaseName:" + dbName);
}
}
}