简介:
Java是一种面向对象,安全灵活的编程语言,已经成为了世界上最流行的编程语言之一。Java语言的一个重要应用领域就是数据库开发。通过Java JDBC(Java Database Connectivity) API,开发人员可以使用Java语言来访问并操作各种数据库。
本篇资料将会详细介绍Java JDBC数据库开发的相关知识,从入门到精通,涵盖了JDBC的基本概念、如何连接数据库、如何执行SQL语句以及如何处理数据库事务等方面。此外,还会给出许多实际例子,来演示如何应用JDBC API来实现各种数据库操作。读者可以通过本篇资料来了解和掌握Java JDBC数据库开发的相关技能。
章节列表:
1. JDBC的基本概念
2. 连接不同类型的数据库
3. 执行SQL语句
4. 处理查询结果集
5. 处理事务
6. JDBC的高级特性
第一章:JDBC的基本概念
Java Database Connectivity(JDBC)是一种用于连接各种关系型数据库的API,它允许Java应用程序通过标准的Java编程语言方式访问数据库,包括连接数据库、执行SQL语句、处理查询结果等操作。
JDBC的三个核心组件:
1. Driver:用于连接数据库的驱动程序。
2. Connection:与数据库的连接对象。
3. Statement:执行SQL语句的对象。
第二章:连接不同类型的数据库
在JDBC中,不同的数据库需要使用特定数据库的驱动程序。常见的数据库有MySQL、Oracle、SQL Server等。
连接MySQL数据库示例:
import java.sql.*;
public class ConnectMySQL {
static final String USER = "username";
static final String PASS = "password";
static final String DB_URL = "jdbc:mysql://localhost:3306/Employee";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Connected database successfully...");
stmt = conn.createStatement();
String sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
System.out.print("ID: " + rs.getInt("id"));
System.out.print(", Age: " + rs.getInt("age"));
System.out.print(", First: " + rs.getString("first"));
System.out.println(", Last: " + rs.getString("last"));
}
rs.close();
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
连接Oracle数据库示例:
import java.sql.*;
public class ConnectOracle {
static final String USER = "username";
static final String PASS = "password";
static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Connected database successfully...");
stmt = conn.createStatement();
String sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
System.out. print("ID: " + rs.getInt("id"));
System.out.print(", Age: " + rs.getInt("age"));
System.out.print(", First: " + rs.getString("first"));
System.out.println(", Last: " + rs.getString("last"));
}
rs.close();
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
第三章:执行SQL语句
在JDBC中,使用Statement和PreparedStatement对象分别执行静态SQL和动态SQL语句。
创建Statement对象执行静态SQL示例:
import java.sql.*;
public class ExecuteStatement {
static final String DB_URL = "jdbc:mysql://localhost:3306/Employee";
static final String USER = "username";
static final String PASS = "password";
static final String INSERT_SQL = "INSERT INTO Employees (id, first, last, age) VALUES (100, 'John', 'Doe', 25)";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Connected database successfully...");
stmt = conn.createStatement();
System.out.println("Executing SQL query...");
stmt.executeUpdate(INSERT_SQL);
System.out.println("SQL query executed successfully...");
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
创建PreparedStatement对象执行动态SQL示例:
import java.sql.*;
public class ExecutePreparedStatement {
static final String DB_URL = "jdbc:mysql://localhost:3306/Employee";
static final String USER = "username";
static final String PASS = "password";
static final String SQL = "INSERT INTO Employees (id, first, last, age) VALUES (?, ?, ?, ?)";
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Connected database successfully...");
pstmt = conn.prepareStatement(SQL);
pstmt.setInt(1, 101);
pstmt.setString(2, "Tom");
pstmt.setString(3, "Smith");
pstmt.setInt(4, 30);
System.out.println("Executing SQL query...");
pstmt.executeUpdate();
System.out.println("SQL query executed successfully...");
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(pstmt!=null)
conn.close();
}catch(SQLException se){
}
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
第四章:处理查询结果集
在JDBC中,通过ResultSet对象来处理查询结果集,包括遍历查询结果、获取指定列的值等操作。
处理查询结果集示例:
import java.sql.*;
public class ProcessResultSet {
static final String DB_URL = "jdbc:mysql://localhost:3306/Employee";
static final String USER = "username";
static final String PASS = "password";
static final String SQL = "SELECT id, first, last, age FROM Employees";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Connected database successfully...");
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL);
while(rs.next()){
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
rs.close();
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
第五章:处理事务
在JDBC中,事务处理对于保证数据的一致性非常重要。通过对Connection对象开启事务、设置保存点、提交或回滚,可以实现对数据库中数据的增删改操作的原子性和持久性。
处理事务示例:
import java.sql.*;
public class ProcessTransaction {
static final String DB_URL = "jdbc:mysql://localhost:3306/Employee";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Connected datab ase successfully...");
conn.setAutoCommit(false);
stmt = conn.createStatement();
String sql1 = "INSERT INTO Employees (id, first, last, age) VALUES (200, 'Jack', 'Jones', 25)";
stmt.executeUpdate(sql1);
String sql2 = "INSERT INTO Employees (id, first, last, age) VALUES (201, 'Mary', 'Smith', 32)";
stmt.executeUpdate(sql2);
Savepoint savepoint = conn.setSavepoint("Savepoint1");
String sql3 = "INSERT INTO Employees (id, first, last, age) VALUES (202, 'Tom', 'Brown', 28)";
stmt.executeUpdate(sql3);
String sql4 = "INSERT INTO Employees (id, first, last, age) VALUES (203, 'Linda', 'White', 41)";
stmt.executeUpdate(sql4);
String sql5 = "INSERT INTO Employees (id, first, last, age) VALUES (204, 'Mike', 'Green', 38)";
stmt.executeUpdate(sql5);
conn.commit();
System.out.println("Transaction committed successfully...");
}catch(SQLException se){
try{
if(conn!=null)
conn.rollback();
}catch(SQLException se2){
se2.printStackTrace();
}
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
第六章:JDBC的高级特性
除了基本的JDBC操作,还有很多高级特性可以进一步优化数据库开发,包括批处理、元数据查询、数据库连接池等。
JDBC批处理示例:
import java.sql.*;
public class ExecuteBatch {
static final String DB_URL = "jdbc:mysql://localhost:3306/Employee";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Connected database successfully...");
stmt = conn.createStatement();
conn.setAutoCommit(false);
stmt.addBatch("INSERT INTO Employees (id, first, last, age) VALUES (300, 'John', 'Doe', 25)");
stmt.addBatch("INSERT INTO Employees (id, first, last, age) VALUES (301, 'Mary', 'Smith', 32)");
stmt.addBatch("INSERT INTO Employees (id, first, last, age) VALUES (302, 'Tom', 'Brown', 28)");
stmt.addBatch("INSERT INTO Employees (id, first, last, age) VALUES (303, 'Linda', 'White', 41)");
stmt.addBatch("INSERT INTO Employees (id, first, last, age) VALUES (304, 'Mike', 'Green', 38)");
int[] count = stmt.executeBatch();
conn.commit();
System.out.println("Batch executed successfully...");
for(int i=0;i<count.length;i++){
System.out.println(count[i] + " row(s) affected.");
}
}catch(SQLException se){
try{
if(conn!=null)
conn.rollback();
}catch(SQLException se2){
se2.printStackTrace();
}
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
JDBC元数据查询可以通过Connection对象的getMetaData方法来获取数据库的元数据信息,包括表结构、列信息、索引信息等。通过元数据查询,可以方便地了解数据库的基本信息,从而更好地进行数据库开发。
元数据查询示例:
文章来源:https://www.toymoban.com/news/detail-433826.html
import java.sql.*;
public class MetadataQuery {
static final String DB_URL = "jdbc:mysql://localhost:3306/Employee";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Connected database successfully...");
DatabaseMetaData dbmd=conn.getMetaData();
System.out.println("Driver Name: "+dbmd.getDriverName());
System.out.println("Driver Version: "+dbmd.getDriverVersion());
System.out.println("Database Name: "+dbmd.getDatabaseProductName());
System.out.println("Database Version: "+dbmd.getDatabaseProductVersion());
System.out.println("Tables:");
ResultSet rs=dbmd.getTables(null,null,null,new String[]{"TABLE"});
while(rs.next()){
System.out.println(rs.getString("TABLE_NAME"));
}
conn.close();
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
数据库连接池是一种重要的数据库优化方案,通过建立连接池,可以避免频繁地创建和销毁数据库连接,从而提高系统性能并减少数据库开销。JDBC连接池可以通过一些现成的开源项目进行实现,包括Apache Commons DBCP和C3P0等。
本篇资料到此结束,希望能够通过学习JDBC,掌握Java对关系型数据库的访问和操作,提高系统性能和稳定性。文章来源地址https://www.toymoban.com/news/detail-433826.html
到了这里,关于JAVA JDBC数据库开发详解:从入门到精通的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!