利用Java实现Oracle与Mysql的JDBC连接,附代码
一、MySQL数据库连接
1、数据库操作
- 删除数据库
DROP DATABASE IF EXISTS USER;
- 创建数据库
CREATE DATABASE USER CHARACTER SET UTF8;
- 使用数据库
USE USER; - 删除数据表
DROP TABLE EMP;
- 创建数据表
CREATE TABLE EMP(
uid INT AUTO_INCREMENT,
username VARCHAR(100),
sex VARCHAR(10),
age INT(4),
CONSTRAINT pk_uid PRIMARY KEY(uid)
);
- 插入数据
insert into emp(username,sex,age)values('wilson','男',10);
2、创建项目,并测试
Java代码实例(for Mysql):
public class DatabaseConnection {
// 取得连接数据库的素材
private static final String URL = "jdbc:mysql://localhost:3306/user?useSSL=true&:characterEncoding=UTF-8";
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String USER = "root";
private static final String PASSWORD = "1234";
private static ThreadLocal<Connection> thread_local = new ThreadLocal<Connection>();
// 加载数据库的驱动包
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 取得数据库的连接
public static Connection getConnection() {
Connection conn = thread_local.get();
if (conn == null) {
try {
conn = DriverManager.getConnection(URL, USER, PASSWORD);
thread_local.set(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return conn;
}
// 关闭数据库
public static void close() {
Connection conn = thread_local.get();
try {
if (conn != null && !conn.isClosed()) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
thread_local.set(null);
}
}
二、Oracle数据库链接
1、创建一个项目
2、导入操作Oracle的驱动包,驱动包的下载本质是一个.class文件,包的加载路径就是classpath。此处,一定要注意JDK版本(服务器则是注意Java运行时版本)、Oracle数据库的版本,从而确定使用合适的OJDBC jar包!
若不清楚Oracle JDBC与JDK的对应关系,请参见官方文档的对应关系
https://www.oracle.com/database/technologies/faq-jdbc.html#01_02
- 截图
(1)导包的基本操作:builderpath—>Configure Builder Path—>Add External JARs–>找到包的路径–>ojdbc6.jar.
3、创建数据连接对象的工具类
(1)准备连接数据库的素材(数据库的用户名,数据库的密码,数据库的连接地址,驱动的名)<范例在>
(2)加载驱动(利用反射的方式加载驱动)
使用静态代码块来加载驱动
Class.froName(驱动的全名称)
(4)取得数据库的连接,把数据库保存在在本地的线程里面ThreadLocal
(5)创建本地的线程的对象,ThreadLocal thread_local = new ThreadLocal();
thread_local.get():取得线程初始化的值
thread_local.set():保存线程初始化的值
(6)关闭数据库的连接
取得本地线程的值,判断是否为空和是否关闭,没有关闭则关闭
Java代码实例(for Oracle):
public class DatabaseConnection {
// 取得连接数据库的素材
private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String USER = "SCOTT";
private static final String PASSWORD = "TIGER";
private static ThreadLocal<Connection> thread_local = new ThreadLocal<Connection>();
// 加载数据库的驱动包
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 取得数据库的连接
public static Connection getConnection() {
Connection conn = thread_local.get();
if (conn == null) {
try {
conn = DriverManager.getConnection(URL, USER, PASSWORD);
thread_local.set(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return conn;
}
// 关闭数据库
public static void close() {
Connection conn = thread_local.get();
try {
if (conn != null && !conn.isClosed()) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
thread_local.set(null);
}
}