JDBC示例（增删查改）
本文共 28904 字，大约阅读时间需要 96 分钟。
前提：
1、项目中引入MySQL的JAR包，POM参考如下配置：
mysql mysql-connector-java 5.1.38
示例：
一、创建数据库
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/"; // Database credentials static final String USER = "root"; static final String PASS = "root"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); // STEP 4: Execute a query System.out.println("Creating database..."); stmt = conn.createStatement(); String sql = "CREATE DATABASE STUDENTS"; stmt.executeUpdate(sql); System.out.println("Database created successfully..."); } catch (SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) stmt.close(); } catch (SQLException se2) { } // nothing we can do try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println("Goodbye!"); }// end main}// end JDBCExample 这将产生如下所示结果：
二、选择数据库
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample2 { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/STUDENTS?serverTimezone=UTC"; // Database credentials static final String USER = "root"; static final String PASS = "root"; public static void main(String[] args) { Connection conn = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); } catch (SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println("Goodbye!"); }// end main}// end JDBCExample 这将产生如下所示结果：
三、删除数据库
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample3 { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/"; // Database credentials static final String USER = "root"; static final String PASS = "root"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); // STEP 4: Execute a query System.out.println("Deleting database..."); stmt = conn.createStatement(); String sql = "DROP DATABASE STUDENTS"; stmt.executeUpdate(sql); System.out.println("Database deleted successfully..."); } catch (SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) conn.close(); } catch (SQLException se) { } // do nothing try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println("Goodbye!"); }// end main}// end JDBCExample 这将产生如下所示结果：
四、创建表
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample4 { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/STUDENTS?serverTimezone=UTC"; // Database credentials static final String USER = "root"; static final String PASS = "root"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); // STEP 4: Execute a query System.out.println("Creating table in given database..."); stmt = conn.createStatement(); String sql = "CREATE TABLE REGISTRATION " + "(id INTEGER not NULL, " + " first VARCHAR(255), " + " last VARCHAR(255), " + " age INTEGER, " + " PRIMARY KEY ( id ))"; stmt.executeUpdate(sql); System.out.println("Created table in given database..."); } catch (SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) conn.close(); } catch (SQLException se) { } // do nothing try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println("Goodbye!"); }// end main}// end JDBCExample 这将产生如下所示结果：
五、删除表
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample5 { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/STUDENTS?serverTimezone=UTC"; // Database credentials static final String USER = "root"; static final String PASS = "root"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); // STEP 4: Execute a query System.out.println("Deleting table in given database..."); stmt = conn.createStatement(); String sql = "DROP TABLE REGISTRATION "; stmt.executeUpdate(sql); System.out.println("Table deleted in given database..."); } catch (SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) conn.close(); } catch (SQLException se) { } // do nothing try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println("Goodbye!"); }// end main}// end JDBCExample 这将产生如下所示结果：
六、插入记录
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample6 { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/STUDENTS?serverTimezone=UTC"; // Database credentials static final String USER = "root"; static final String PASS = "root"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); // STEP 4: Execute a query System.out.println("Inserting records into the table..."); stmt = conn.createStatement(); String sql = "INSERT INTO Registration " + "VALUES (100, 'Zara', 'Ali', 18)"; stmt.executeUpdate(sql); sql = "INSERT INTO Registration " + "VALUES (101, 'Mahnaz', 'Fatma', 25)"; stmt.executeUpdate(sql); sql = "INSERT INTO Registration " + "VALUES (102, 'Zaid', 'Khan', 30)"; stmt.executeUpdate(sql); sql = "INSERT INTO Registration " + "VALUES(103, 'Sumit', 'Mittal', 28)"; stmt.executeUpdate(sql); System.out.println("Inserted records into the table..."); } catch (SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) conn.close(); } catch (SQLException se) { } // do nothing try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println("Goodbye!"); }// end main}// end JDBCExample 这将产生如下所示结果：
七、查询表记录
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample7 { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/STUDENTS?serverTimezone=UTC"; // Database credentials static final String USER = "root"; static final String PASS = "root"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); // STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql = "SELECT id, first, last, age FROM Registration"; ResultSet rs = stmt.executeQuery(sql); // STEP 5: Extract data from result set while (rs.next()) { // Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); // Display values 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) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) conn.close(); } catch (SQLException se) { } // do nothing try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println("Goodbye!"); }// end main}// end JDBCExample 这将产生如下所示结果：
八、更新表记录
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample8 { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/STUDENTS?serverTimezone=UTC"; // Database credentials static final String USER = "root"; static final String PASS = "root"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); // STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql = "UPDATE Registration " + "SET age = 30 WHERE id in (100, 101)"; stmt.executeUpdate(sql); // Now you can extract all the records // to see the updated records sql = "SELECT id, first, last, age FROM Registration"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { // Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); // Display values 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) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) conn.close(); } catch (SQLException se) { } // do nothing try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println("Goodbye!"); }// end main}// end JDBCExample 这将产生如下所示结果：
九、删除记录
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample9 { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/STUDENTS?serverTimezone=UTC"; // Database credentials static final String USER = "root"; static final String PASS = "root"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); // STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql = "DELETE FROM Registration " + "WHERE id = 101"; stmt.executeUpdate(sql); // Now you can extract all the records // to see the remaining records sql = "SELECT id, first, last, age FROM Registration"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { // Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); // Display values 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) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) conn.close(); } catch (SQLException se) { } // do nothing try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println("Goodbye!"); }// end main}// end JDBCExample 这将产生如下所示结果：
十、Where子句
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample10 { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/STUDENTS?serverTimezone=UTC"; // Database credentials static final String USER = "root"; static final String PASS = "root"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); // STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); // Extract records without any condition. System.out.println("Fetching records without condition..."); String sql = "SELECT id, first, last, age FROM Registration"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { // Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); // Display values System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); } // Select all records having ID equal or greater than 101 System.out.println("Fetching records with condition..."); sql = "SELECT id, first, last, age FROM Registration" + " WHERE id >= 101 "; rs = stmt.executeQuery(sql); while (rs.next()) { // Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); // Display values 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) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) conn.close(); } catch (SQLException se) { } // do nothing try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println("Goodbye!"); }// end main}// end JDBCExample 这将产生如下所示结果：
十一、Like子句
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample11 { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/STUDENTS?serverTimezone=UTC"; // Database credentials static final String USER = "root"; static final String PASS = "root"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); // STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); // Extract records without any condition. System.out.println("Fetching records without condition..."); String sql = "SELECT id, first, last, age FROM Registration"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { // Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); // Display values System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); } // Select all records having ID equal or greater than 101 System.out.println("Fetching records with condition..."); sql = "SELECT id, first, last, age FROM Registration" + " WHERE first LIKE '%za%' "; rs = stmt.executeQuery(sql); while (rs.next()) { // Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); // Display values 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) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) conn.close(); } catch (SQLException se) { } // do nothing try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println("Goodbye!"); }// end main}// end JDBCExample 这将产生如下所示结果：
十二、排序
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample12 { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/STUDENTS?serverTimezone=UTC"; // Database credentials static final String USER = "root"; static final String PASS = "root"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); // STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); // Extract records in ascending order by first name. System.out.println("Fetching records in ascending order..."); String sql = "SELECT id, first, last, age FROM Registration" + " ORDER BY first ASC"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { // Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); // Display values System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); } // Extract records in descending order by first name. System.out.println("Fetching records in descending order..."); sql = "SELECT id, first, last, age FROM Registration" + " ORDER BY first DESC"; rs = stmt.executeQuery(sql); while (rs.next()) { // Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); // Display values 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) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) conn.close(); } catch (SQLException se) { } // do nothing try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println("Goodbye!"); }// end main}// end JDBCExample 这将产生如下所示结果：
测试工程：
==>如有问题，请联系我：easonjim#163.com，或者下方发表评论。<==转载地址：http://vnvpa.baihongyu.com/
你可能感兴趣的文章
零元学Expression Blend 4 - Chapter 46 三分钟快速充电-设定Margin的小撇步
查看>>
Format Conditions按条件显示表格记录
查看>>
RichTextBox指定全部文字显示不同颜色及部分文字高亮颜色显示
查看>>
mysql优化----explain的列分析
查看>>
Python正则表达式
查看>>
Java中CAS详解
查看>>
Spring Boot Unregistering JMX-exposed beans on shutdown
查看>>
命令行man的帮助手册
查看>>
Ubuntu 16.04下为Android编译OpenCV 3.2.0 Manager
查看>>
poi 导入导出的api说明（大全）
查看>>
Fix-Mapped Addresses
查看>>
fmt标签如何计算两个日期之间相隔的天数
查看>>
Spark核心技术原理透视一（Spark运行原理）
查看>>
《Gradle权威指南》--Gradle任务
查看>>
IntelliJ IDEA创建文件时自动填入作者时间 定制格式
查看>>
Android app启动activity并调用onCreate()方法时都默默地干了什么？
查看>>
远程监视jboss应用java内存的配置
查看>>
前端如何接收 websocket 发送过来的实时数据
查看>>
JavaWeb下载文件response
查看>>
Laravel的三种安装方法总结
查看>>