北京 网站开发,北京网站整站优化,合作建网站,苏州网站开发建设一、基础概念
1.1JDBC 步骤
导入驱动jar包
注册驱动
获取数据库连接对象 Connection
DataSource dSource;
dSource.getConnection();
定义sql语句
String sql update account set balance 500 where id 1;
获取执行sql语句的对象 Statement
PreparedStatement…一、基础概念
1.1JDBC 步骤
导入驱动jar包
注册驱动
获取数据库连接对象 Connection
DataSource dSource;
dSource.getConnection();
定义sql语句
String sql update account set balance 500 where id 1;
获取执行sql语句的对象 Statement
PreparedStatement stmt conn. prepareStatement(sql)
执行sql接受返回结果int count stmt.executeUpdate(sql);
处理结果 System.out.println(count);
释放资源 stmt.close(); conn.close();1.2相关类介绍 1.3相关代码 在Maven中声明MySQL的JDBC实现依赖 1.3.1查询
package org.example;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class SelectTest {public static void main(String[] args) {// 注册 MySQL 驱动try {Class.forName(com.mysql.cj.jdbc.Driver);System.out.println(注册驱动成功!);} catch (ClassNotFoundException e1) {System.out.println(注册驱动失败!);e1.printStackTrace();return;}String url jdbc:mysql://localhost:3306/t_book?serverTimezoneUTC;Connection conn null;try {// 构建 Java 和数据库之间的桥梁URL用户名密码conn DriverManager.getConnection(url, root, b123456);System.out.println(连接数据库成功);// 构建数据库执行者Statement stmt conn.createStatement();System.out.println(创建 Statement 成功);// 执行 SQL 语句并返回结果到 ResultSetResultSet rs stmt.executeQuery(select bookid, bookname, price from book order by bookid);// 开始遍历 ResultSet 数据while (rs.next()) {System.out.println(rs.getInt(1) , rs.getString(2) , rs.getInt(price));}rs.close();stmt.close();} catch (SQLException e) {e.printStackTrace();} finally {try {if (conn ! null) {conn.close();System.out.println(关闭数据库连接成功);}} catch (SQLException e) {e.printStackTrace();}}}
}1.3.2增删改
package org.example;import java.sql.*;public class UpdateTest {public static void main(String[] args) {executeUpdate(update book set price 300 where bookid 1);executeUpdate(insert into book(bookid, bookname, price) values(4, 编译原理, 90));executeUpdate(delete from book where bookid 4);}/*** 执行数据更新操作* param sql 要执行的 SQL 语句*/public static void executeUpdate(String sql) {// 注册 MySQL 驱动try {Class.forName(com.mysql.cj.jdbc.Driver);System.out.println(注册驱动成功!);} catch (ClassNotFoundException e1) {System.out.println(注册驱动失败!);e1.printStackTrace();}String url jdbc:mysql://localhost:3306/t_book?serverTimezoneUTC;Connection conn null;try {// 构建 Java 和数据库之间的桥梁URL用户名密码conn DriverManager.getConnection(url, root, b123456);System.out.println(连接数据库成功);// 构建数据库执行者Statement stmt conn.createStatement();System.out.println(创建 Statement 成功);int rs stmt.executeUpdate(sql);stmt.close();} catch (SQLException e) {e.printStackTrace();} finally {try {if (conn ! null) {conn.close();System.out.println(关闭数据库连接成功);}} catch (SQLException e) {e.printStackTrace();}}}
}1.3.3事务逻辑 package org.example;import java.sql.*;public class TransactionTest {public static void main(String[] args) throws Exception {// 构建Java和数据库之间的桥梁介质try {Class.forName(com.mysql.cj.jdbc.Driver);System.out.println(注册驱动成功!);} catch (ClassNotFoundException e1) {System.out.println(注册驱动失败!);e1.printStackTrace();}String url jdbc:mysql://localhost:3306/t_book?serverTimezoneUTC;Connection conn null;try {// 构建Java和数据库之间的桥梁URL用户名密码conn DriverManager.getConnection(url, root, b123456);conn.setAutoCommit(false);insertBook(conn, insert into book values(101, aaaa, 10));insertBook(conn, insert into book values(102, bbbb, 10));insertBook(conn, insert into book values(103, cccc, 10));Savepoint phase1 conn.setSavepoint(); // 设置一个保存点insertBook(conn, insert into book values(104, cccc, 10));insertBook(conn, insert into book values(105, cccc, 10));conn.rollback(phase1); // 回滚到phase1保存点即上面2行无效conn.commit();System.out.println(操作成功);} catch (SQLException e) {e.printStackTrace();conn.rollback();} finally {if (null ! conn) {conn.close();}}}public static void insertBook(Connection conn, String sql) {try {// 构建数据库执行者Statement stmt conn.createStatement();// 执行SQL语句int result stmt.executeUpdate(sql);stmt.close();} catch (SQLException e) {e.printStackTrace();}}
}1.3.4拼接字符串 package org.example;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;public class InsertTest {public static void main(String[] a) {//concatInsertBook();unsafeConcatInsertBook();}public static void concatInsertBook() {// 构建Java和数据库之间的桥梁介质try {Class.forName(com.mysql.cj.jdbc.Driver);System.out.println(注册驱动成功!);} catch (ClassNotFoundException e1) {System.out.println(注册驱动失败!);e1.printStackTrace();}String url jdbc:mysql://localhost:3306/t_book?serverTimezoneUTC;Connection conn null;try {// 构建Java和数据库之间的桥梁URL用户名密码conn DriverManager.getConnection(url, root, b123456);// 构建数据库执行者Statement stmt conn.createStatement();System.out.println(创建Statement成功);// 执行SQL语句int bookid 11;String bookName Effective Java;int price 50;String sql insert into book(bookid,bookname,price) values( bookid , bookName , price );int result stmt.executeUpdate(sql);stmt.close();System.out.println(操作成功);} catch (SQLException e) {e.printStackTrace();} finally {try {if (conn ! null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}}public static void unsafeConcatInsertBook() {// 构建Java和数据库之间的桥梁介质try {Class.forName(com.mysql.cj.jdbc.Driver);System.out.println(注册驱动成功!);} catch (ClassNotFoundException e1) {System.out.println(注册驱动失败!);e1.printStackTrace();}String url jdbc:mysql://localhost:3306/t_book?serverTimezoneUTC;Connection conn null;try {// 构建Java和数据库之间的桥梁URL用户名密码conn DriverManager.getConnection(url, root, b123456);// 构建数据库执行者Statement stmt conn.createStatement();System.out.println(创建Statement成功);// 执行SQL语句int bookid 13;String bookName Effective Java,50);delete from t_book;insert into t_book values(101, faked book;int price 50;String sql insert into book(bookid,bookname,price) values( bookid , bookName , price );;System.out.println(sql);int result stmt.executeUpdate(sql);stmt.close();System.out.println(操作成功);} catch (SQLException e) {e.printStackTrace();} finally {try {if (conn ! null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}}
}1.3.5batch函数
package org.example;import java.sql.*;public class InsertTest1 {public static void main(String[] a) {safeInsertBook();batchInsertBook();}public static void safeInsertBook() {Connection conn null;try {// 注册 MySQL 驱动Class.forName(com.mysql.jdbc.Driver);System.out.println(注册驱动成功!);} catch (ClassNotFoundException e1) {System.out.println(注册驱动失败!);e1.printStackTrace();}String url jdbc:mysql://localhost:3306/t_book?serverTimezoneUTC;try {// 构建Java和数据库之间的桥梁URL用户名密码conn DriverManager.getConnection(url, root, b123456);String sql insert into book(bookid,bookname,price) values (?, ?, ?);// 构建数据库执行者PreparedStatement pstmt conn.prepareStatement(sql);// 设置参数int bookid 15;String bookName Effective Java,50);delete from t_book;insert into t_book values(101, faked book;int price 50;pstmt.setInt(1, bookid);pstmt.setString(2, bookName);pstmt.setInt(3, price);// 执行SQL语句int result pstmt.executeUpdate();pstmt.close();System.out.println(操作成功);} catch (SQLException e) {e.printStackTrace();} finally {try {if (conn ! null) {conn.close();System.out.println(关闭数据库连接成功);}} catch (SQLException e) {e.printStackTrace();}}}public static void batchInsertBook() {Connection conn null;try {// 注册 MySQL 驱动Class.forName(com.mysql.jdbc.Driver);System.out.println(注册驱动成功!);} catch (ClassNotFoundException e1) {System.out.println(注册驱动失败!);e1.printStackTrace();}String url jdbc:mysql://localhost:3306/t_book?serverTimezoneUTC;try {// 构建Java和数据库之间的桥梁URL用户名密码conn DriverManager.getConnection(url, root, b123456);String sql insert into book(bookid,bookname,price) values (?,?,?);// 构建数据库执行者PreparedStatement pstmt conn.prepareStatement(sql);// 设置参数String bookName aaaaaaaaaaaaaaaa;int price;// 执行批量插入操作for (int i 210; i 202; i) {price 50;pstmt.setInt(1, i);pstmt.setString(2, bookName);pstmt.setInt(3, price);pstmt.addBatch();}pstmt.executeBatch();pstmt.close();System.out.println(操作成功);} catch (SQLException e) {e.printStackTrace();} finally {try {if (conn ! null) {conn.close();System.out.println(关闭数据库连接成功);}} catch (SQLException e) {e.printStackTrace();}}}
}如果有大量的sq语句它们结构相同仅仅差别在具体数值上那么可以通过addBatch 方法进行批量操作。这样会提高性能减少数据库负担。 1.3.6ResultSetMetaData类 package org.example;import java.sql.*;public class ResultSetMetaDataTest {public static void main(String[] args) {// 构建Java和数据库之间的桥梁介质try {Class.forName(com.mysql.cj.jdbc.Driver);System.out.println(注册驱动成功!);} catch (ClassNotFoundException e1) {System.out.println(注册驱动失败!);e1.printStackTrace();return;}String url jdbc:mysql://localhost:3306/t_book?serverTimezoneUTC;Connection conn null;try {// 构建Java和数据库之间的桥梁URL用户名密码conn DriverManager.getConnection(url, root, b123456);// 构建数据库执行者Statement stmt conn.createStatement();System.out.println(创建Statement成功);// 执行SQL语句并返回结果到ResultSetResultSet rs stmt.executeQuery(select bookid, bookname, price from book order by bookid);// 获取结果集的元数据ResultSetMetaData meta rs.getMetaData();int cols meta.getColumnCount();for (int i 1; i cols; i) {System.out.println(meta.getColumnName(i) , meta.getColumnTypeName(i));}rs.close();stmt.close();} catch (SQLException e) {e.printStackTrace();} finally {try {if (conn ! null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}}
} 二、数据库连接池
2.1流程重述 2.2亨元模式 享元模式, Flyweight Pattern - 经典23个设计模式的一种属于结构型模式。 - 一个系统中存在大量的相同的对象由于这类对象的大量使用会造成系统内存的耗费可以使用享元模式来减少系统中对象的数量。 2.3 数据库连接池概念 2.4C3P0连接池
2.4.1配置文件 !-- https://mvnrepository.com/artifact/com.mchange/c3pθ --dependencygroupIdcom.mchange/groupIdartifactIdc3p0/artifactIdversion0.9.5.2/version/dependency!-- https://mvnrepository.com/artifact/com.alibaba/druid --dependencygroupIdcom.alibaba/groupIdartifactIddruid/artifactIdversion1.1.10/version/dependency
2.4.2相关代码
package org.example;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class SelectTest1 {public static void main(String[] args) {Connection conn null;try {// 从c3p0获取数据库连接conn C3p0Factory1.getConnection();//conn C3p0Factory2.getConnection();从Druid获取conn DruidFactory1.getConnection();//conn DruidFactory2.getConnection();// 构建数据库执行者Statement stmt conn.createStatement();System.out.println(创建Statement成功);// 执行SQL语句并返回结果到ResultSetResultSet rs stmt.executeQuery(select bookid, bookname, price from book order by bookid);// 开始遍历ResultSet数据while (rs.next()) {System.out.println(rs.getInt(1) , rs.getString(2) , rs.getInt(price));}rs.close();stmt.close();} catch (Exception e) {e.printStackTrace();} finally {try {if (conn ! null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}}
}2.4.3相关配置 ?xml version1.0 encodingUTF-8?
c3p0-configdefault-config !-- 默认配答 --property namedriverClasscom.mysql.jdbc.Driver/propertyproperty namejdbcUrljdbc:mysql://localhost:3306/test/propertyproperty nameuserroot/propertyproperty namepassword123456/propertyproperty nameinitialPoolSize5/propertyproperty namemaxPoolSize20/property/default-config
/c3p0-configpackage org.example;import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;public class C3p0Factory2 {private static ComboPooledDataSource dataSource null;public static void init() throws Exception {// 创建 ComboPooledDataSource 实例会自动加载 c3p0-config.xml 文件的配置dataSource new ComboPooledDataSource();// 此时 dataSource 是一个完全配置好的可用连接池 DataSource}public static Connection getConnection() throws Exception {if (null dataSource) {init();}// 返回从连接池获取的数据库连接return dataSource.getConnection();}
}2.4.4 DruidData类
package org.example;import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;public class DruidFactory1 {private static DruidDataSource dataSource null;public static void init() throws Exception {dataSource new DruidDataSource();dataSource.setDriverClassName(com.mysql.jdbc.Driver);dataSource.setUsername(root);dataSource.setPassword(b123456);dataSource.setUrl(jdbc:mysql://localhost:3306/t_book?serverTimezoneUTC);dataSource.setInitialSize(5);dataSource.setMinIdle(1);dataSource.setMaxActive(10);// 启用监控统计功能dataSource.setFilters(stat);}public static Connection getConnection() throws Exception {if (null dataSource) {init();}return dataSource.getConnection();}
} 2.4.6配置
driverClassNamecom.mysql.jdbc.Driver
urljdbc:mysql://localhost:3306/t_book?serverTimezoneUTC
usernameroot
passwordb123456
filtersstat
initialSize2
maxActive300
maxWait60000
timeBetweenEvictionRunsMillis60000
minEvictableIdleTimeMillis300000
validationQuerySELECT 1
testWhileIdletrue
testOnBorrowfalse
testOnReturnfalse
poolPreparedStatementsfalse
maxPoolPreparedStatementPerConnectionSize200
package org.example;import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;public class DruidFactory2 {private static DruidDataSource dataSource null;public static void init() throws Exception {Properties properties new Properties();// 加载属性文件 druid.propertiesInputStream in DruidFactory2.class.getClassLoader().getResourceAsStream(druid.properties);properties.load(in);// 通过属性创建 Druid 数据源dataSource (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);in.close();}public static Connection getConnection() throws Exception {if (null dataSource) {init();}return dataSource.getConnection();}
}
三、基于web的数据库开发
3.1配置DataSource 3.2相关类driverClassName驱动类 3.3Connection接口 java.sql.Connection接口表示与特定数据库的会话。在Connection的上下文中执行SQL语句并返回结果。Connection接口对于连接到数据库和执行SQL语句至关重要。 3.4Statement接口 Statement接口是Java执行数据库操作的一个重要接口用于在已经建立数据库连接的基础上向数据库发送要执行的SQL语句。Statement接口提供了执行语句和获取结果的基本方法。PreparedStatement接口添加了处理IN参数的方法而CallableStatement添加了处理OUT参数的方法。 3.5PreparedStatement接口 PreparedStatement接口是Java中的一个接口它是用来执行预编译的SQL语句的。PreparedStatement是Statement的子接口继承了Statement的所有功能。PreparedStatement接口提供了补充占位符变量的方法
