JDBC的常用方法
JDBC是一个java处理mysql的一个方法库和驱动
链接JDBC
使用可以获取连接
Connection connection = DriverManager.getConnection(urlString,user,password);
urlString的格式为（注意引号和问号）：
192.168.101.121：服务器地址
3306：端口
database：链接的数据库
jdbc:mysql://192.168.101.121:3306/"+database+"?useSSL=false&serverTimezone=CST&characterEncoding=utf-8
useSSL=false&serverTimezone=CST&characterEncoding=utf-8为传递的属性，字面意思能理解
获取mysql对象
链接获取好后要获取mysql对象Statement （声明的意思）
Statement sql = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
一般createStatement()无参就行，里面的参数下面会提
查询（是否为立即执行，和游标的移动）
执行查询
executeQuery(执行查询的意思)
ResultSet rSet = sql.executeQuery("select * from "+tableName);
执行查询就不能进行增删等操作，如果进行增删需要使用executeUpdate()
sql.executeUpdate("update tb_user set score = 59 where id = 5;");
当然execute()都能执行
获取到的资源会存到一个ResultSet 中
遍历ResultSet ：
i是第几列的元素（从1开始）
rSet.next()会将游标移到下一行（什么是游标-下面）
while (rSet.next()) {
System.out.print(rSet.getString(i));
}
游标：
理解成一个卡子，初始状态卡在第一行的前面，每个rSet.next()会将卡子移到下一行
，每次rSet.getString(i)获取的都是卡子所在（刚经过）一行的第i列的元素
如果我们在上面createStatement()时没有参数，那么游标就只能下移动（不太确定，不过不重要）
下面是createStatement的用法
createStatement(int type, int concurrency)
type的取值决定滚动的方式：
Concurrency取值觉得是否可以用结果集更新数据（防止只查询却更改了数据库的误操作）
滚动查询经常用到ResultSet的下述方法
获取行数
rSet.last();
int row = rSet.getRow();
getRow()会记录游标上面的行
预处理语句
就是把mysql命令提前翻译成mysql的底层指令传给mysql数据库软件，加快执行速度
PreparedStatement pre = con.prepareStatement(String sql)
pre.execute()//执行sql
通配符
直接举例子：
?我们可以使用
pre.setFloat(1,65)
进行赋值，1是第一个？的意思，65为值pre = con.prepareStatment("select * from >booklist where price < ?") pre.setFloat(1,65) pre.execute()//执行sql
另外有setDate,setDouble等不同设置类型
事务
还是举个例子，比如我转账，大体有两个步骤：1.我扣钱 2.他收钱。不能我扣钱执行成功了，二他收钱就失败了。如果他失败了我们就要把我扣钱的步骤给撤回。
实现的原理：关闭自动提交模式，让结果集reSult就算是执行了execute()也不会立马提交到mysql数据库
关闭自动提交：con.setAutoCommit(false);
开启自动提交：con.setAutoCommit(true);
例如：
con.setAutoCommit(false);
sql.executeUpdate("update tb_user set score = 59 where id = 5;");
sql.executeUpdate("update tb_user set score = 59 where id = 6;");
con.setAutoCommit(true);
只有当con.setAutoCommit(true);时才会真正执行
分页显示
下面这个例子就是分页显示的例子，我们需要用到几个方法：
ResultSetMetaData rsmData = (ResultSetMetaData) rSet.getMetaData();
int columnCount = rsmData.getColumnCount();
rsmData.getColumnName(1+i);
Result的getMetaData方法可以获得一个ResultSetMetaData，ResultSetMetaData可以使用getColumnCount获得mysql的列数
getColumnName(i)可以获得第i列的名字
例子
在前面我们已经知道了MVC模式，下面时使用MVC模式的一个数据库链接例子
结构：
input.jsp为登录界面
show.jsp为展示界面
jdbc_Servlet.java为处理程序
Record_bean为对象数据bean
input.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Insert title here</title>
<style type="text/css">
.bg {
width: 300px;
height: 255px;
margin: 200px auto;
background-color: gray;
}
</style>
</head>
<body>
<div class="bg">
<form action="servlet" method="post">
<table>
<tr><td>数据库：</td><td><input type="text" name="database"></td></tr>
<tr><td>表名：</td><td><input type="text" name="tableName"></td></tr>
<tr><td>用户名：</td><td><input type="text" name="user"></td></tr>
<tr><td>密码：</td><td><input type="text" name="password"></td></tr>
<tr><td><input type="submit" value="提交"></td></tr>
</table>
</form>
</div>
</body>
</html>
show.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Insert title here</title>
</head>
<jsp:useBean id="recordBean" class="com.ljq.Record_bean" scope="session"/>
<!-- 通过表单的参数设置bean -->
<jsp:setProperty name="recordBean" property="pageSize" param="pageSize"/>
<jsp:setProperty name="recordBean" property="currentPage" param="currentPage"/>
<body>
<table border=2>
<%
String [][] table = recordBean.getTableRecord();
String [] columnName = recordBean.getColumnName();
if (table == null || columnName == null){
out.print("没有记录");
return;
}
out.print("<tr>");
for (int i = 0; i < columnName.length; i++){
out.print("<th>"+columnName[i]+"</th>");
}
out.print("</tr>");
int CurrentPage = recordBean.getCurrentPage();
int totalRecord = table.length;
int pageSize = recordBean.getPageSize();
int totalPage = recordBean.getTotalPage();
if (totalRecord % pageSize == 0){
totalPage = totalRecord / pageSize;
}
else {
totalPage = totalRecord / pageSize + 1;
}
recordBean.setTotalPage(totalPage);
if (CurrentPage < 0){
recordBean.setCurrentPage(recordBean.getTotalPage());
CurrentPage = recordBean.getTotalPage();
}
else if (CurrentPage > recordBean.getTotalPage()){
recordBean.setCurrentPage(1);
CurrentPage = 1;
}
int start = (recordBean.getCurrentPage() - 1) * pageSize;
for (int i = start; i < pageSize + start; i++){
if (i == recordBean.getTotalRecord()){
break;
}
out.print("<tr>");
for (int j = 0; j < recordBean.getColumnName().length; j++){
out.print("<td>"+table[i][j]+"</td>");
}
out.print("</tr>");
}
%>
</table>
</body>
</html>
jdbc_Servlet.java
package com.ljq;
public class Record_bean {
String []columnName=null;
String [][] tableRecord = null;
int pageSize = 3;
int totalPage = 1;
int currentPage = 1;
int totalRecord = 0;
public String[] getColumnName() {
return columnName;
}
public void setColumnName(String[] columnName) {
this.columnName = columnName;
}
public String[][] getTableRecord() {
return tableRecord;
}
public void setTableRecord(String[][] tableRecord) {
this.tableRecord = tableRecord;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getTotalRecord() {
return totalRecord;
}
public void setTotalRecord(int totalRecord) {
this.totalRecord = totalRecord;
}
}
Record_bean.java
package com.ljq;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.catalina.User;
import org.omg.CORBA.PUBLIC_MEMBER;
import com.mysql.cj.Session;
import com.mysql.cj.jdbc.result.ResultSetMetaData;
public class jdbc_Servlet extends HttpServlet{
public static void main(String[] args) {
// TODO Auto-generated method stub
}
public void init() throws ServletException {
// TODO Auto-generated method stub
super.init();
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
req.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
String user = req.getParameter("user");
String password = req.getParameter("password");
String database = req.getParameter("database");
String tableName = req.getParameter("tableName");
if (user.length() == 0 || password.length() == 0 || database.length() == 0 || tableName.length() == 0) {
resp.sendRedirect("input.jsp");
return;
}
Connection connection;
Statement sql;
HttpSession session = req.getSession(true);
Record_bean record_bean = new Record_bean();
String urlString = "jdbc:mysql://192.168.101.121:3306/"+database+"?useSSL=false&serverTimezone=CST&characterEncoding=utf-8";
try {
connection = DriverManager.getConnection(urlString,user,password);
sql = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rSet = sql.executeQuery("select * from "+tableName);
ResultSetMetaData rsmData = (ResultSetMetaData) rSet.getMetaData();
int columnCount = rsmData.getColumnCount();
String []columnName = new String[columnCount];
for (int i = 0; i < columnCount; i++) {
columnName[i] = rsmData.getColumnName(1+i);
}
record_bean.setColumnName(columnName);
rSet.last();
int row = rSet.getRow();
String [][] tableRecordStrings = new String[row][columnCount];
rSet.beforeFirst();
int j = 0;
while (rSet.next()) {
for (int i = 0; i < columnCount; i++) {
tableRecordStrings[j][i] = rSet.getString(i+1);
}
j++;
}
record_bean.setTotalRecord(j);
record_bean.setTableRecord(tableRecordStrings);
session.setAttribute("recordBean", record_bean);
connection.close();
resp.sendRedirect("show.jsp");
} catch (Exception e) {
// TODO: handle exception
resp.getWriter().print("<h2>"+e);
System.out.println(e);
}
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
doPost(req, resp);
}
2527
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
