Java Web 数据库的连接
一. 连接 mysql 数据库
1.驱动下载
下载连接:https://dev.mysql.com/downloads/
进入页面点击Community,选择下方的MySQL Connectors,选择Connector/J,如下图所示。
进入页面Select Operating System: 选择Platform Independent ,选择完后页面如下,点击Download即可下载,压缩文件。如下图所示。
2.连接数据库准备
驱动下载完成解压后,复制根目录下的 :mysql-connector-java-8.0.15.jar 文件到Web项目的 WEB-INF/lib 目录下。然后鼠标右键项目,选择Build Path 在Libraries 里点击Classpath选择添加 Add JAR 选择项目目录下的 mysql-connector-java-8.0.15.jar 文件添加。
3.连接数据库
(1.利用反射类加载数据库驱动类:
mysql 8.x驱动类位置为:com.mysql.cj.jdbc.Driver
中间多了一个 .cj
反射加载驱动类:Class.forName("com.mysql.cj.jdbc.Driver");
URL:"jdbc:mysql://localhost:3306/数据库名",User,password
(2.实例代码(servlet实现):
package mysql;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/Mysql")
public class Mysql extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
Connection conn = null;
Statement stat = null;
ResultSet resu = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC", "root", “"*************"); //获取数据库连接
stat = conn.createStatement();
resu = stat.executeQuery("select * from city where CountryCode='CHN'"); //执行查询返回结果
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
out.println("china city info: <br/><br/>");
ResultSetMetaData meta = resu.getMetaData(); //获取元数据
int ColumnCount = meta.getColumnCount();
for (int i = 1; i < ColumnCount; i++) {
String name = meta.getColumnName(i); //获取列名
out.println(name + " ");
}
while (resu.next()) {
for (int i = 1; i < ColumnCount; i++) {
String data = resu.getString(meta.getColumnName(i)); //根据列名获取数据
out.println(data + " ");
}
out.println("<br/>");
}
out.flush();
out.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("数据库驱动加载失败!");
} catch (SQLException e) {
e.printStackTrace();
System.out.println("获取数据库连接失败!");
} finally {
try {
resu.close();
stat.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("数据库关闭失败");
}
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
4.访问页面
二.连接 SqlServer 数据库
1.驱动下载
下载连接:https://www.microsoft.com/zh-cn/download/details.aspx?id=11774
进入页面点击下载,选择驱动下载即可。
2.连接数据库准备
与 mysql 操作类似。
3.连接数据库
(1.利用反射类加载数据库驱动类:
SqlServer jdbc驱动6.0 驱动类位置为:"com.microsoft.sqlserver.jdbc.SQLServerDriver"
反射加载驱动类:Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
url:jdbc:sqlserver://localhost:1433;DatabaseName=数据库名;user=***;password=****
(2.实例代码(jsp实现):
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.ResultSetMetaData" %>
<%@ page import="java.sql.SQLException" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<div align="center">
<%
Connection conn = null;
Statement stat = null;
ResultSet resu = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(
"jdbc:sqlserver://localhost:1433;DatabaseName=BooksMS;user=Admin;password=2438675");
stat = conn.createStatement();
resu = stat.executeQuery("select * from Books");
response.setContentType("text/html;charset=UTF-8");
out.println("ALL Books info: <br/><br/>");
%><table border="1"><%
ResultSetMetaData meta = resu.getMetaData();
int ColumnCount = meta.getColumnCount();
%><tr align="center"><%
for (int i = 1; i < ColumnCount; i++) {
%><td><%
String name = meta.getColumnName(i);
out.println(name + " ");
%></td><%
}
%></tr><%
while (resu.next()) {
%><tr align="center"><%
for (int i = 1; i < ColumnCount; i++) {
%><td><%
String data = resu.getString(meta.getColumnName(i));
out.println(data + " ");
%></td><%
}
%></tr><%
}
%></table><%
//out.flush();
//out.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("数据库驱动加载失败!");
} catch (SQLException e) {
e.printStackTrace();
System.out.println("获取数据库连接失败!");
} finally {
try {
resu.close();
stat.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("数据库关闭失败");
}
}
%>
</div>
</body>
</html>
4.访问页面
完…