JDBC操作

目录

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操作
**引用包是最大的困难（一步一步来）**