Javaweb
第五章. JDBC 访问数据库
5.1 MySQL
如何连接数据库
Maven 中添加MySQL依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
1. 直接注册驱动(不推荐使用)
(dbUrl别忘了加 ?serverTimezone=UTC 时区参数)
try {
String dbUrl = "jdbc:mysql://127.0.0.1:3306/girls?serverTimezone=UTC";
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取数据库连接(里面内容依次是:"jdbc:mysql://主机名:端口号/数据库名","用户名","登录密码")
Connection connection = DriverManager.getConnection(dbUrl,"root","2251");
String sql = "SELECT * FROM `products`";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getString(2) + "\t");
}
//6.关闭jdbc连接
statement.close();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
System.out.println("ERROR" + e.getMessage());
}
2. 新建数据库配置文件,获取配置文件信息后,再注册数据库驱动
- 在src——main——resources目录下,新建db.properties文件
driver=com.mysql.cj.jdbc.Driver
#在和mysql传递数据的过程中,使用unicode编码格式,并且字符集设置为utf-8
url=jdbc:mysql://127.0.0.1:3306/shopping?serverTimezone=UTC
user=root
password=2251
-
新建utilities包,然后在里面创建JdbcUtil类,利用反射获取db.properties文件信息,最后返回数据库连接
package utilities; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.util.Properties; //获取到db.properties文件中的数据库信息 public class JdbcUtil { //私有变量 private static String driver; private static String url; private static String user; private static String password; //静态块 static { try { //1.新建属性集对象 Properties properties = new Properties(); //2通过反射,新建字符输入流,读取db.properties文件 InputStream input = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"); //3.将输入流中读取到的属性,加载到properties属性集对象中 properties.load(input); //4.根据键,获取properties中对应的值 driver = properties.getProperty("driver"); url = properties.getProperty("url"); user = properties.getProperty("user"); password = properties.getProperty("password"); } catch (Exception e) { e.printStackTrace(); } } //返回数据库连接 public static Connection getConnection() { try { //注册数据库的驱动 Class.forName(driver); //获取数据库连接(里面内容依次是:主机名和端口、用户名、密码)返回数据库连接 return DriverManager.getConnection(url, user, password); } catch (Exception e) { e.printStackTrace(); } return null; } }
-
调用JdbcUtil类返回的数据库连接操作数据库
import utilities.JdbcUtil; import java.sql.*; public class LinkMySQL { public static void main(String[] args) throws SQLException { //获取数据库连接 Connection connection = JdbcUtil.getConnection(); String sql = "SELECT * FROM `products`"; assert connection != null; Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()) { System.out.println(resultSet.getString(3) + "\t"); } //6.关闭jdbc连接 statement.close(); connection.close(); } }
3. 连接池技术
原生方式
-
在webapp文件夹下新增META-INF文件夹 下面建立context.xml文件
webapp -------- META-INF --------
<?xml version="1.0" encoding="UTF-8" ?> <Context reloadable = "true"> <Resource name = "jdbc/shoppingDS" type = "javax.sql.DataSource" maxTotal = "4" maxIdle = "2" driverClassName = "com.mysql.cj.jdbc.Driver" url = "jdbc:mysql://127.0.0.1:3306/shopping?serverTimezone=UTC" username = "root" password = "2251" maxWaitMills = "5000" /> </Context>
-
测试
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String prefix = "java:comp/env/"; try { Context context = new InitialContext(); DataSource dataSource = (DataSource) context.lookup(prefix + "jdbc/shoppingDS"); Connection connection = dataSource.getConnection(); System.out.println(connection); } catch (SQLException e) { e.printStackTrace(); } catch (NamingException e) { e.printStackTrace(); } }
c3p0方式
-
在src——main——resources下增加c3p0-config.xml文件
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <default-config> <!-- 配置数据库驱动 --> <property name="driverClass">com.mysql.cj.jdbc.Driver</property> <!-- 配置数据库链接地址 --> <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/shopping?serverTimezone=UTC</property> <!-- 配置数据库用户名 --> <property name="user">root</property> <!-- 配置数据库密码 --> <property name="password">2251</property> <!-- 扩展配置 --> <!-- 获取连接超时设置,默认是一直等待,单位毫秒 --> <property name="checkoutTimeout">30000</property> <!--每多少秒检查所有连接池中的空闲连接。Default: 0 --> <property name="idleConnectionTestPeriod">30</property> <!-- 初始化连接池的连接数 --> <property name="initialPoolSize">10</property> <!--最大空闲时间,多少秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 --> <property name="maxIdleTime">30</property> <!--连接池中保留的最大连接数。Default: 15 --> <property name="maxPoolSize">100</property> <!-- 连接池中保留的最小连接数 --> <property name="minPoolSize">10</property> <!--JDBC的标准参数,用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。如果maxStatements与maxStatementsPerConnection均为0,则缓存被关闭。Default:0 --> <property name="maxStatements">200</property> <!--maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0 --> <property name="maxStatementsPerConnection">200</property> <!--c3p0是异步操作的,缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能 通过多线程实现多个操作同时被执行。Default:3 --> <property name="numHelperThreads">3</property> </default-config> </c3p0-config>
-
在src——main——java——utilities里添加DataSourceUtils类
package utilities; import com.mchange.v2.c3p0.ComboPooledDataSource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DataSourceUtils { private static ComboPooledDataSource ds = new ComboPooledDataSource(); /** * 获取数据源 * * @return 连接池 */ public static DataSource getDataSource() { return ds; } /** * 释放资源 * * @param conn * @param st * @param rs */ public static void CloseResource(Connection conn, Statement st, ResultSet rs) { closeResultSet(rs); closeStaement(st); closeConn(conn); } /** * 获取连接 * * @return 连接 * @throws SQLException */ public static Connection getConnection() throws SQLException { return ds.getConnection(); } /** * 释放连接 * * @param conn 连接 */ public static void closeConn(Connection conn) { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } finally { conn = null; } } } /** * 释放语句执行者 * * @param st 语句执行者 */ public static void closeStaement(Statement st) { if (st != null) { try { st.close(); } catch (SQLException e) { e.printStackTrace(); } finally { st = null; } } } /** * 释放结果集 * * @param rs 结果集 */ public static void closeResultSet(ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { rs = null; } } } }
-
新建C3p0Select类,用数据库连接池的方式测试
import com.mchange.v2.c3p0.ComboPooledDataSource; import utilities.DataSourceUtils; import javax.sql.DataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class C3p0Select { public static void main(String[] args) throws SQLException { Connection connection = DataSourceUtils.getConnection(); String sql = "SELECT * FROM products"; Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()) { System.out.println(resultSet.getString(2) + "\t"); } //6.关闭jdbc连接 statement.close(); connection.close(); } }
使用DAO模式设计
customer为例:
Customer数据类
在main ----- java -----建立model文件夹下建一个Customer数据类:
package model;
import java.io.Serializable;
public class Customer implements Serializable {
private int id;
private String name;
private String email;
private double balance;
public Customer() {}
public Customer(String name, String email, double balance) {
this.name = name;
this.email = email;
this.balance = balance;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
BaseDao
package dao;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
public interface BaseDao {
static DataSource getDateSource() {
DataSource dataSource = null;
try {
Context context = new InitialContext();
dataSource = (DataSource) context.lookup("java:comp/env/jdbc/shoppingDS");
} catch (NamingException ne) {
System.out.println("Exception:" + ne);
}
return dataSource;
}
default Connection getConnection() {
DataSource dataSource = getDateSource();
Connection conn = null;
try {
conn = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
DaoException
package dao;
public class DaoException extends Exception{
private static final long serialVersionUID = 19192L;
private String message;
public DaoException() {}
@Override
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
@Override
public String toString() {
return message;
}
}
CustomerDao
package dao.customerDao;
import dao.BaseDao;
import dao.DaoException;
import model.Customer;
import java.util.ArrayList;
public interface CustomerDao extends BaseDao {
boolean addCustomer(Customer customer) throws DaoException;
Customer findById(int id) throws DaoException;
ArrayList<Customer> findAllCustomer() throws DaoException;
boolean deleteCustomerById(int id) throws DaoException;
}
CustomerDaoImpl
package dao.customerDao;
import dao.DaoException;
import model.Customer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class CustomerDaoImpl implements CustomerDao {
private int initId = 1001;
@Override
public boolean addCustomer(Customer customer) throws DaoException {
String sql = "INSERT INTO customers VALUES(?,?,?,?)";
try (
Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
) {
pstmt.setInt(1, customer.getId());
pstmt.setString(2, customer.getName());
pstmt.setString(3, customer.getEmail());
pstmt.setDouble(4, customer.getBalance());
pstmt.execute();
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
@Override
public Customer findById(int id) throws DaoException {
String sql = "SELECT * FROM customers WHERE id = ?";
Customer customer = new Customer();
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
) {
pstmt.setInt(1, id);
try (ResultSet resultSet = pstmt.executeQuery()) {
if (resultSet.next()) {
customer.setId(resultSet.getInt("id"));
customer.setName(resultSet.getString("name"));
customer.setEmail(resultSet.getString("email"));
customer.setBalance(resultSet.getDouble("balance"));
}
}
} catch (SQLException e) {
e.printStackTrace();
return null;
}
return customer;
}
@Override
public ArrayList<Customer> findAllCustomer() throws DaoException {
ArrayList<Customer> customers = new ArrayList<>();
String sql = "SELECT * FROM customers";
try (Connection conn = getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery()
) {
System.out.println(conn);
while (resultSet.next()) {
Customer customer = new Customer();
customer.setId(resultSet.getInt("id"));
customer.setName(resultSet.getString("name"));
customer.setEmail(resultSet.getString("email"));
customer.setBalance(resultSet.getDouble("balance"));
customers.add(customer);
}
return customers;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
@Override
public boolean deleteCustomerById(int id) throws DaoException {
String sql = "DELETE FROM customers WHERE id=?";
try (
Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
) {
pstmt.setInt(1, id);
pstmt.execute();
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
}
引入Layui(可以下载)
项目结构
放于webapp下
使用
在jsp界面里面加入头部信息
<head>
<meta charset="utf-8"/>
<title>Layui</title>
<meta name="renderer" content="webkit"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"/>
<meta
name="viewport"
content="width=device-width, initial-scale=1, maximum-scale=1"
/>
<link rel="stylesheet" href="/layui/css/layui.css" media="all"/>
// 自定义的样式
<style>
.form-body {
margin-top: 20px;
}
</style>
</head>
script导入
<script src="/layui/layui.js" charset="utf-8"></script>
<script>
layui.use(["form"]);
</script>
使用DAO
index.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<meta charset="utf-8"/>
<title>Layui</title>
<meta name="renderer" content="webkit"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"/>
<meta
name="viewport"
content="width=device-width, initial-scale=1, maximum-scale=1"
/>
<link rel="stylesheet" href="/layui/css/layui.css" media="all"/>
<style>
.form-body {
margin-top: 20px;
}
</style>
</head>
<body>
<%
String msg = (String) request.getAttribute("result");
if (msg != null) {
%>
<blockquote class="layui-elem-quote"><%=msg%>
</blockquote>
<%} else {%>
<blockquote class="layui-elem-quote">数据库操作</blockquote>
<%}%>
<div class="layui-container">
<div class="layui-row form-body">
<div class="layui-col-md5">
<%@include file="WEB-INF/jsp/displayAllCustomer.jsp"%>
</div>
<div class="layui-col-md5 layui-col-md-offset1">
<%@include file="WEB-INF/jsp/insertCoustomer.jsp"%>
</div>
</div>
</div>
<script src="/layui/layui.js" charset="utf-8"></script>
<script>
layui.use(["form"]);
</script>
</body>
</html>
displayAllCustomer.jsp
<%@ page import="dao.customerDao.CustomerDaoImpl" %>
<%@ page import="model.Customer" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="dao.DaoException" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<table class="layui-table">
<!-- <colgroup>
<col width="120" />
<col width="200" />
<col />
</colgroup> -->
<thead>
<tr>
<th>ID</th>
<th>用户名</th>
<th>Email</th>
<th>余额</th>
<th>操作</th>
</tr>
</thead>
<tbody>
<%
CustomerDaoImpl dao = new CustomerDaoImpl();
try {
ArrayList<Customer> customers = dao.findAllCustomer();
for (Customer customer : customers) {
%>
<tr>
<td><%=customer.getId()%>
</td>
<td><%=customer.getName()%>
</td>
<td><%=customer.getEmail()%>
</td>
<td><%=customer.getBalance()%>
</td>
<td><a style="color: blue" href="/deleteCustomer?id=<%=customer.getId()%>">删除记录</a></td>
</tr>
<%
}
} catch (DaoException e) {
e.printStackTrace();
}
%>
</tbody>
</table>
insertCoustomer.jsp
<%--
Created by IntelliJ IDEA.
User: linxi
Date: 2020/12/11
Time: 19:53
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<form class="layui-form" action="insertCustomer" method="post">
<div class="layui-form-item">
<label class="layui-form-label">客户名:</label>
<div class="layui-input-inline">
<input
type="text"
name="name"
lay-verify="required"
placeholder="请输入客户名"
autocomplete="off"
class="layui-input"
/>
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">Email:</label>
<div class="layui-input-block">
<input
type="text"
name="email"
lay-verify="email"
placeholder="请输入Email"
autocomplete="off"
class="layui-input"
/>
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">余额:</label>
<div class="layui-input-block">
<input
type="text"
name="balance"
lay-verify="required|number"
placeholder="请输入余额"
autocomplete="off"
class="layui-input"
/>
</div>
</div>
<div class="layui-form-item">
<div class="layui-input-block">
<button
type="submit"
class="layui-btn"
lay-submit=""
>
插入数据
</button>
<button type="reset" class="layui-btn layui-btn-primary">
重置
</button>
</div>
</div>
</form>
运行截图
感觉layui挺好看的