# JDBC_Test

**Repository Path**: linxi2251/JDBC_Test

## Basic Information

- **Project Name**: JDBC_Test
- **Description**: java 连接数据库的几种方式
- **Primary Language**: Unknown
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2020-12-10
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# Javaweb
[项目如何导入IDEA](https://gitee.com/linxi2251/webStudy)  

**此文档只是学习记录时候的迭代文档,不代表最终代码**

## 第五章. JDBC 访问数据库

## 5.1 MySQL

### 如何连接数据库

**Maven 中添加MySQL依赖**

```html
<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.21</version>
 </dependency>
```



##### 1. 直接注册驱动（不推荐使用）

（dbUrl别忘了加  **?serverTimezone=UTC** 时区参数）

```java
 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. 新建数据库配置文件，获取配置文件信息后，再注册数据库驱动

1. 在src——main——resources目录下，新建db.properties文件

```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
```

2. 新建utilities包，然后在里面创建JdbcUtil类，利用反射获取db.properties文件信息，最后返回数据库连接

   ```java
   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;
       }
   }
   ```

   

   

3. 调用JdbcUtil类返回的数据库连接操作数据库

   ```jav
   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. 连接池技术

###### 原生方式

1. 在webapp文件夹下新增**META-INF**文件夹 下面建立**context.xml**文件

   webapp -------- META-INF --------

   ```html
   <?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>
   ```

2. 测试

   ```java
   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方式

1. 在src——main——resources下增加c3p0-config.xml文件

   ```html
   <?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>
   ```

2. 在src——main——java——utilities里添加DataSourceUtils类

   ```java
   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;
               }
           }
       }
   }
   ```

   

3. 新建C3p0Select类，用数据库连接池的方式测试

   ```java
   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数据类:

```java
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

```java
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

```java
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

```java
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

```java
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(可以下载)

https://www.layui.com/

#### 项目结构

![image-20201211200231448](README.assets/image-20201211200231448.png)

#### 放于webapp下

<img src="README.assets/image-20201211200357640.png" alt="image-20201211200357640"  />



#### 使用

在jsp界面里面加入头部信息

```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导入

```jsp
<script src="/layui/layui.js" charset="utf-8"></script>
<script>
    layui.use(["form"]);
</script>
```

### 使用DAO

#### index.jsp

```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

```java
<%@ 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

```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挺好看的

![image-20201211201046973](README.assets/image-20201211201046973.png)

### 换一个界面(交互性更强)

![image-20201213141136143](README.assets/image-20201213141136143.png)

![image-20201213141158310](README.assets/image-20201213141158310.png)