目录
JDBC的相关操作,突出的代码是核心API。
JDBC驱动连接数据库
确定使用的数据库类型,直接在网上搜索连接代码就行了,代码片
.
Connection con = null;//数据库连接对象
try {
Class.forName("数据库驱动:Driver");
String url = "数据库路径";
String user = "数据库用户名";
String pass = "数据库用户密码";
con = DriverManager.getConnection(url, user, pass);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
读取资源文件配置数据库连接
代码片
.
//工具类的类:
public class EX_SQL{
...
public void propertiesParam(String paramFile) throws Exception {
Properties properties = new Properties();
//在项目中读取:InputStream inputStream =你那工具类的类名.class.getResourceAsStream("/文件名.properties");
InputStream inputStream =EX_SQL.class.getResourceAsStream(paramFile);
properties.load(inputStream);//读取/access.properties配置文件中的键值
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
pass = properties.getProperty("pass");
}
}
资源文件access.properties,代码片
.
driver=com.hxtt.sql.access.AccessDriver
url=jdbc:Access:///F:HrMS.accdb
user=
pass=
数据库ResultSetMetaData对象
网上的代码,代码片
.
package text;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
//获取表的属性,如列名,列数,
public class sqlname {
public static Connection getConnection() {
Connection con = null;
try {
Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();//Access数据库驱动
String url = "jdbc:Access:///F:HrMS.accdb";//数据库是F:HrMS.accdb
String user = "";//用户名
String pass = "";//密码
conn = DriverManager.getConnection(url, user, pass);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return con;
}
public static void main(String[] args) {
Connection con = getConnection();
String sql = "select * from user";
PreparedStatement stmt;
try {
stmt = con.prepareStatement(sql);
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData data = rs.getMetaData();
for (int i = 1; i <= data.getColumnCount(); i++) {
// 获得所有列的数目及实际列数
int columnCount = data.getColumnCount();
// 获得指定列的列名
String columnName = data.getColumnName(i);
// 获得指定列的列值
int columnType = data.getColumnType(i);
// 获得指定列的数据类型的名
String columnTypeName = data.getColumnTypeName(i);
// 所在的Catalog名字
String catalogName = data.getCatalogName(i);
// 对应数据类型的类
String columnClassName = data.getColumnClassName(i);
// 在数据库中类型的最大字符个数
int columnDisplaySize = data.getColumnDisplaySize(i);
// 默认的列的标题
String columnLabel = data.getColumnLabel(i);
// 获得列的模式
String schemaName = data.getSchemaName(i);
// 某列类型的精确度(类型的长度)
int precision = data.getPrecision(i);
// 小数点后的位数
int scale = data.getScale(i);
// 获取某列对应的表名
String tableName = data.getTableName(i);
// 是否自动递增
boolean isAutoInctement = data.isAutoIncrement(i);
// 在数据库中是否为货币型
boolean isCurrency = data.isCurrency(i);
// 是否为空
int isNullable = data.isNullable(i);
// 是否为只读
boolean isReadOnly = data.isReadOnly(i);
// 能否出现在where中
boolean isSearchable = data.isSearchable(i);
System.out.println(columnCount);
System.out.println("获得列" + i + "的字段名称:" + columnName);
System.out.println("获得列" + i + "的类型,返回SqlType中的编号:" + columnType);
System.out.println("获得列" + i + "的数据类型名:" + columnTypeName);
System.out.println("获得列" + i + "所在的Catalog名字:" + catalogName);
System.out.println("获得列" + i + "对应数据类型的类:" + columnClassName);
System.out.println("获得列" + i + "在数据库中类型的最大字符个数:" + columnDisplaySize);
System.out.println("获得列" + i + "的默认的列的标题:" + columnLabel);
System.out.println("获得列" + i + "的模式:" + schemaName);
System.out.println("获得列" + i + "类型的精确度(类型的长度):" + precision);
System.out.println("获得列" + i + "小数点后的位数:" + scale);
System.out.println("获得列" + i + "对应的表名:" + tableName);
System.out.println("获得列" + i + "是否自动递增:" + isAutoInctement);
System.out.println("获得列" + i + "在数据库中是否为货币型:" + isCurrency);
System.out.println("获得列" + i + "是否为空:" + isNullable);
System.out.println("获得列" + i + "是否为只读:" + isReadOnly);
System.out.println("获得列" + i + "能否出现在where中:" + isSearchable);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
显示页面
代码片
.
<%@page import="org.omg.CORBA.PRIVATE_MEMBER"%>
<%@ page contentType="text/html; charset=utf-8" language="java"
import="java.sql.*" errorPage=""%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>返回结果集</title>
<jsp:useBean id="text" class="com.ExecuteSQL" scope="page" />
<jsp:setProperty property="url" param="url" name="text" />
<jsp:setProperty property="driver" param="driver" name="text" />
<jsp:setProperty property="user" param="user" name="text" />
<jsp:setProperty property="pass" param="pass" name="text" />
<jsp:setProperty property="sql" param="sql" name="text" />
</head>
<body>
<h2 align="center"><strong>返回结果集</strong></p>
<%
ResultSet rs;
int x=text.executeSql();
Statement st=text.getStatement();
if(x==1){
String s1="<table align=\"center\" border=\" 1 \" cellspacing=\"0\">";
out.print(s1);
rs = st.getResultSet();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
out.print("<tr>");
for (int i = 0; i < columnCount; i++) {
out.print("<td>");
out.print(rsmd.getColumnName(i+1));
out.print("</td>");
System.out.println(rsmd.getColumnName(i+1) + "\t");
}
out.print("<tr>");
while (rs.next()) {
out.print("<tr>");
for (int i = 0; i < columnCount; i++) {
out.print("<td>");
out.print(rs.getString(i + 1));
out.print("</td>");
System.out.println(rs.getString(i + 1) + "\t");
}
out.print("</tr>");
System.out.println();
}
out.print("</table>");
}else if(x==0){
out.print("该sql语句影响的记录有" + st.getUpdateCount() + "条");
}else if(x==-1){
System.out.println("异常");
}
%>
</body>
</html>
JDBC中的Statement的execute()方法
,代码片
.
package com;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import com.hxtt.concurrent.r;
/* 1. Statement的execute()语句可以执行任何SQL语句,但是它比较麻烦,故通常应该使用executeQuery()或者executeUpdate()方法。
* 2. 使用exrcute()方法执行只是返回boolean值,它表示执行该SQL语句是否返回了ResultSet对象,如果需要获取ResultSet对象的话,可以使用下面两个方法:
* (1)getResultSet()获取该Statement执行查询语句后返回的ResultSet对象
* (2)getUpdateCount()获取该Statement执行SQL语句后影响的记录行数
* 3.以下为代码示例,具体讲解穿插在代码中
* */
public class ExecuteSQL {
// 常规操作
private String driver;
private String url;
private String user;
private String pass;
private Statement statement;
private String sql;
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPass() {
return pass;
}
public void setPass(String pass) {
this.pass = pass;
}
public Statement getStatement() {
return statement;
}
public void setStatement(Statement statement) {
this.statement = statement;
}
public String getSql() {
return sql;
}
public void setSql(String sql) {
this.sql = sql;
}
public void propertiesParam() throws Exception {
Properties properties = new Properties();
// 在项目中读取:InputStream inputStream
// =你那工具类的类名.class.getResourceAsStream("/文件名.properties");
InputStream inputStream = ExecuteSQL.class.getResourceAsStream("/access.properties");
properties.load(inputStream);// 读取/access.properties配置文件中的键值
this.setDriver(properties.getProperty("driver"));
this.setUrl(properties.getProperty("url"));
this.setUser(properties.getProperty("user"));
this.setPass(properties.getProperty("pass"));
}
// jdbc中统一操作数据库的方法
public int executeSql() {
// 连接数据库
int x = -1;
try {
propertiesParam();
// 连接数据库
Class.forName(this.getDriver());
Connection connection = DriverManager.getConnection(this.getUrl(), this.getUser(), this.getPass());
this.setStatement(connection.createStatement());
boolean hasResultSet = statement.execute(sql); // 执行execute方法后返回boolean值,统一操作数据库方法(增删改查/表-数据)
if (hasResultSet) { // 判断是否有返回集合,查找
x = 1;// 有返回值
} else {// 没有结果集返回,更新
x = 0;// 没有返回值
}
} catch (Exception e) {
x = -1;// 异常
}
return x;
}
}
JDBC增删改查
Statement.executeQuery() 查询
代码片
.
Connection con = DriverManager.getConnection(url, "", "");// 数据库连接对象
Statement sta = con.createStatement();//数据库对象
ResultSet re = null;//查询结果集
//执行查找
{
re = sta.executeQuery("select * from user");
// 执行查找,%表示一个或者多个
re = sta.executeQuery("select name,height from user where name like '张%'");
// 执行查找,_表示一个
re = sta.executeQuery("select name,height from user where name like '张_'");
// 执行查找,[ ]表示任意一个
re = sta.executeQuery("select name,height from user where name like '[张李]%'");
// 排序查找 asc升序(默认)、desc降序
re = sta.executeQuery("select name,height from user order by height desc");
}
ResultSetMetaData data = re.getMetaData();//数据对象
for (int i = 1; i <= data.getColumnCount(); i++) {// 列号必须从1开始,获得返回re中有多少列
System.out.print(" " + data.getColumnName(i));// 获得返回re中的列名
}
while(re.next()){
System.out.print(" "+re.getInt("number"));//属性名
System.out.print(" "+re.getString("name"));
System.out.print(" "+re.getDate("birthday"));
System.out.print(" "+re.getFloat("height"));
}
随机查询,使用游标随意移动查询,开启
代码片
.
package com;
import java.sql.*;
import java.util.*;
//随机查询,使用游标随意移动查询
public class text1_create2_buffer4{
public static void main(String args[]){
ArrayList<Integer> list=new ArrayList<Integer>();
Connection con;
Statement sql;
ResultSet rs;
try{ Class.forName("com.hxtt.sql.access.AccessDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:Access:///F:HrMS.accdb","","");
//开启随机移动、结果集不可更新数据库 sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs=sql.executeQuery("select * from user");
//游标移动到最后一行
rs.last();
//获得游标所在行号。即最后一行
int lastNumber=rs.getRow();
for(int i=1;i<=lastNumber;i++) //随机容器--获得随机抽取的记录数
list.add(i);
double sum=0;
int samplingNumber=3;
int count=samplingNumber;
System.out.println("随机抽取"+count+"条记录:");
while(samplingNumber>0){
int i=(int)(Math.random()*list.size());//随机产生0-list.size()内的数
int index=list.get(i);
//用随机容器内的随机数,游标移到index这一行
rs.absolute(index);
int number=rs.getInt(1);
String name=rs.getString(2);
java.sql.Date birth=rs.getDate(3);
double height=rs.getDouble("height");
System.out.println(number+","+name+","+birth+","+height);
sum=sum+height;
samplingNumber--;
//list删除抽取过的元素,防止随机容器产生重复
list.remove(i);
}
System.out.println(count+"条记录的平均身高:"+sum/count);
con.close();
}
catch(SQLException e1) {}
}
}
更新数据库Statement.executeUpdate()
代码片
.
Connection con = DriverManager.getConnection(url, "", "");// 数据库连接对象
Statement sta = con.createStatement();//数据库对象
ResultSet re = null;//查询结果集
//执行更新
{
//插入
int ok=sta .executeUpdate
("insert into user value('007','将林','1988-12-20',1.75)");
// 修改
sta .executeUpdate
("update user set height=1.89 where number='001'");
//删除
rs=sql.executeQuery("delete from user where name='王二'");
//查找
re=sta .executeQuery("select * from user");
}
用结果集更新数据库,要开启结果集更新和移动到插入行
代码片
.
package com;
import java.sql.*;
import java.util.*;
//用结果集更新数据库
public class text2_update2_boffer1{
public static void main(String args[]){
Connection con;
Statement sql;
ResultSet rs;
Calendar calendar=Calendar.getInstance();//建立一个日历对象
calendar.set(2020,9,10); //注意9代表十月,将日历翻到2020-10-10
java.sql.Date date=new java.sql.Date(calendar.getTimeInMillis()); //用日历时间建立时间对象
try { Class.forName("com.hxtt.sql.access.AccessDriver ");
}
catch(ClassNotFoundException e){
System.out.println(""+e);
}
try{
con=DriverManager.getConnection("jdbc:Access:///F:HrMS.accdb","","");
//开启随机读取、结果集更新数据库
sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs=sql.executeQuery("select * from user");
//游标移动到第3行
rs.absolute(3);
//修改第2列的数据,将user表第3行记录的name的值更新为“王晓二”
rs.updateString(2,"王晓二");
//执行更新数据
rs.updateRow();
//将游标移动到插入行
rs.moveToInsertRow();
rs.updateString(1,"005"); //建立插入行数据
rs.updateString(2, "陈大林"); //建立插入行数据
rs.updateDate(3,date); //建立插入行数据
rs.updateDouble(4,1.79); //建立插入行数据
//向user表将插入行数据插入其中
rs.insertRow();
rs=sql.executeQuery("select * from user");
while(rs.next()){
String number=rs.getString(1);
String name=rs.getString(2);
java.sql.Date birth=rs.getDate(3);
double height=rs.getDouble(4);
System.out.println(number+","+name+","+birth+","+height);
}
con.close();
}
catch(SQLException e){
System.out.println(e);
}
}
}
缓冲结果集:CachedRowSetImpl默认受访问限制的API
代码片
.
package com;
import java.sql.*;
import com.sun.rowset.*;
//缓冲结果集
//CachedRowSetImpl默认受访问限制的API
public class text3_cachedrowsetlmpl_ChuanChong{
public static void main(String args[]){
Connection con;
Statement sql;
ResultSet rs;
//缓冲对象
CachedRowSetImpl rowSet;
try {
Class.forName("com.hxtt.sql.access.AccessDriver ");
}
catch(ClassNotFoundException e){
System.out.println(""+e);
}
try{
con=DriverManager.getConnection("jdbc:Access:///F:HrMS.accdb","","");
sql=con.createStatement();
rs=sql.executeQuery("select * from user");
//创建缓冲对象
rowSet=new CachedRowSetImpl();
//将结果集放入缓冲对象
rowSet.populate(rs);
con.close();//现在就可以关闭连接了
while(rowSet.next()){
String number=rowSet.getString(1);
String name=rowSet.getString(2);
Date birth=rowSet.getDate(3);
double height=rowSet.getDouble(4);
System.out.println(number+","+name+","+birth+","+height);
}
con.close();
}
catch(SQLException e){
System.out.println(e);
}
}
}
预处理语句 con.prepareStatement(sql)
代码片
.
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.sun.rowset.*; //特殊的CachedRowSetImpl包
import javax.sql.rowset.CachedRowSet;
//预处理语句,运行更快,在java时就已经转化为数据库底层语言
//使用通配符
public class text4_prepare {
public static void main(String[] args) {
try {
Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
} catch (InstantiationException | IllegalAccessException | ClassNotFoundException e1) {
e1.printStackTrace();
}
Connection con = null;// 不要在异常处理块里定义变量
//通配符
String sql = "select * from user where height<?";
ResultSet rs=null;
//预处理对象
PreparedStatement preparedStatement = null;
//定义缓冲集合
CachedRowSetImpl cachedRowSetImpl ;
try {
con = DriverManager.getConnection("jdbc:Access:///F:HrMS.accdb", "", "");
//预处理对象
preparedStatement = con.prepareStatement(sql);
//从左向右第一个通配符赋值1.60f
preparedStatement.setFloat(1, 1.70f);
//提交访问
rs = preparedStatement.executeQuery();
cachedRowSetImpl=new CachedRowSetImpl();//实现缓冲集合
cachedRowSetImpl.populate(rs);//将rs存入缓冲集合
rs.close();//关闭rs
while(cachedRowSetImpl.next()){//CachedRowSetImpl类继承了ResultSet类的绝大数方法,可以向操作ResultSet一样操作cachedRowSetImpl对象
System.out.print(" "+cachedRowSetImpl.getInt(1));
System.out.print(" "+cachedRowSetImpl.getString(2));
System.out.print(" "+cachedRowSetImpl.getDate(3));
System.out.print(" "+cachedRowSetImpl.getFloat(4));
System.out.println("");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
JDBC事务,要关闭自动提交,多次提交sql,最后提交事务
代码片
.
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//建立事务,要么所以的sql语句同时完成,要么都不完成,回滚
public class text5_ShiWu {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection con = null;
try {
Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
con = DriverManager.getConnection("jdbc:Access:///F:HrMS.accdb", "", "");
// 关闭自动提交
con.setAutoCommit(false);
Statement st = con.createStatement();
String sql = "select *from user where name='麻子'or name='王二' ";
ResultSet re = st.executeQuery(sql);// 查找
while(re.next()){
System.out.print(" " + re.getInt(1));
System.out.print(" " + re.getString(2));
System.out.print(" " + re.getDate(3));
System.out.print(" " + re.getFloat(4));
System.out.println("");
}
sql = "update user set height ='1.90f' where name='麻子'";//都要带上单引号''
// 修改,返回修改的条数
int n = st.executeUpdate(sql);// 修改
System.out.println(n);//返回修改的条数
sql = "update message set height ='1.50f' where name='王二'";
// 修改,返回修改的条数
int m = st.executeUpdate(sql);// 修改
System.out.println(m);//返回修改的条数
sql = "select *from message where name='麻子'or name='王二' ";
// 修改,返回修改的条数
re = st.executeQuery(sql);// 查找
while(re.next()){
System.out.print(" " + re.getInt(1));
System.out.print(" " + re.getString(2));
System.out.print(" " + re.getDate(3));
System.out.print(" " + re.getFloat(4));
System.out.println("");
}
// 提交事务
con.commit();
} catch (InstantiationException | IllegalAccessException | ClassNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
System.out.println("e1");
}catch (SQLException e) {
// TODO: handle exception
System.out.println("e");
try {
//当事务中有一条sql执行失败,将回滚事务,撤回到commit之前的状态
con.rollback();
} catch (SQLException e2) {
// TODO Auto-generated catch block
System.out.println("e2");
e2.printStackTrace();
}
}
}
}
批处理操作-更新操作,关闭自动提交,rs.addBatch(sql)添加,rs.executeBatch()提交
代码片
.
kage com;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
//批处理操作-更新操作
public class text6_PiChuLi {
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
} catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Connection con = null;
try {
con = DriverManager.getConnection("jdbc:Access:///F:HrMS.accdb","","");
//关闭自动提交
con.setAutoCommit(false);
Statement rs=con.createStatement(); //数据库对象
String sql="update user set height ='1.90f' where name='麻子'";
//添加操作
rs.addBatch(sql);
sql="update user set height ='1.00f' where name='王二'";
//添加操作
rs.addBatch(sql);
sql="update user set birthday = '2020-5-1' where name='李四'";
//添加操作
rs.addBatch(sql);
//提交更新,返回提交成功的sql的序号
int [] n=rs.executeBatch();
System.out.println(n.length);
//开启事务
// con.commit();
//清空
rs.clearBatch();
} catch (SQLException e) {
// TODO Auto-generated catch block
try {
//回滚
// con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
}
}
总结
1. 随机查询
2. 用结果集更新数据库
3. 缓冲
4. 预处理
5. 事务
6. 批处理
7. 对应JDBC的API操作
**引用包是最大的困难(一步一步来)**