JDBC之数据库的增，删，改，查, 搜, 批量执行，获取表结构,获取某一用户下所有的表,滚轴操作的封装

/**
 * 
 */
package com.HUXU.action;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

/**
 * @author HU-XU
 *把数据库的增，删，改，查, 搜,
 *批量执行，获取表结构,获取某一用户下所有的表,滚轴操作的封装。
 */
public class JdbcDButil {
	
	//增
	public void insertDate(String name,String classes,int score) throws Exception{
		Connection cn=ToolDButil.getConnection();
		String sqlstr="INSERT stuinfo(name,classes,score)VALUES(?,?,?)";//增数据库
		PreparedStatement ps=cn.prepareStatement(sqlstr);
		ps.setString(1, "Tom");//增加
		ps.setString(2, "one");
		ps.setInt(3, 100);
		ps.executeUpdate();
		ToolDButil.free02(ps, cn);
		this.readDate();
	}
	
	//删
	public void deleDate() throws Exception{
		Connection cn=ToolDButil.getConnection();
		String sqlstr="DELETE FROM stuinfo WHERE score=?";//删除数据
		PreparedStatement ps=cn.prepareStatement(sqlstr);
		ps.setInt(1, 100);//删除
		ps.executeUpdate();
		ToolDButil.free02(ps, cn);
		this.readDate();
	}
	
	//改
	public void updateDate()throws Exception{
		Connection cn=ToolDButil.getConnection();
		String sqlstr="UPDATE stuinfo SET name=? WHERE score=?";//增数据库
		PreparedStatement ps=cn.prepareStatement(sqlstr);
		ps.setString(1, "Tom");//修改
		ps.setInt(2, 99);
		ps.executeUpdate();
		ToolDButil.free02(ps, cn);
		this.readDate();
	}
	

	//查
	public void readDate() throws Exception{
		Connection cn=ToolDButil.getConnection();
		Statement st=cn.createStatement();
		String sqlstr="SELECT * FROM stuinfo";//查数据库
		ResultSet rs=st.executeQuery(sqlstr);
		while(rs.next()){//遍历结果
			System.out.println(rs.getObject(1));
		}
		ToolDButil.free01(cn, st, rs);
	}
	
	//搜
	public void searchDate() throws Exception{
		Connection cn=ToolDButil.getConnection();
		String sqlstr="SELECT * FROM stuinfo WHERE name LIKE ?";//搜索数据
		PreparedStatement ps=cn.prepareStatement(sqlstr);
		ps.setString(1, "%T%");//搜索
		ResultSet rs=ps.executeQuery();
		while(rs.next()){//遍历结果
			System.out.println(rs.getObject(1));
		}
		ToolDButil.free02(ps, cn);
		this.readDate();
	}
	
	//批量执行
	public void batchDate()throws Exception{
		Connection cn=ToolDButil.getConnection();
		Statement st=cn.createStatement();
		String sqlstr="CREATE TABLE YWGAME (ID NUMBER PRIMARY KEY,GAMENAME VARCHAR2(50) NOT NULL)";
		st.addBatch(sqlstr);
		st.addBatch("INSERT INTO YWGAME VALUES(1,'热血传奇')");
		st.addBatch("INSERT INTO YWGAME VALUES(2,'梦幻西游')");
		st.executeBatch();
		ToolDButil.free01(cn, st, null);
	}
	
	//获取表结构
	public void structureDate()throws Exception{
		Connection cn=ToolDButil.getConnection();
		Statement st=cn.createStatement();
		String sqlstr="SELECT * FROM stuinfo";//查询的表
		ResultSet rs = st.executeQuery(sqlstr);
		ResultSetMetaData rsmd = rs.getMetaData(); //获取元数据
		int numberOfColumns = rsmd.getColumnCount();//列数
		for(int i=1;i<=numberOfColumns;i++){
			System.out.print(rsmd.getColumnName(i)+"\t");
		}
		while(rs.next()){
			System.out.println();
			for(int i=1;i<=numberOfColumns;i++){
				System.out.print(rs.getObject(i)+"\t");//getObject()能够智能匹配字段类型
			}
		}
		ToolDButil.free01(cn, st, null);
	}
	
	//获取某一用户下所有的表
	public void tablesDate()throws Exception{
		Connection cn=ToolDButil.getConnection();
		DatabaseMetaData dbm=cn.getMetaData();
		ResultSet rs=dbm.getTables(null,"SCOTT",null,new String[]{"TABLE","VIEW"});		
		
		ResultSetMetaData rsmd = rs.getMetaData(); //获取元数据
		int numberOfColumns = rsmd.getColumnCount();//列数
		for(int i=1;i<=numberOfColumns;i++){
			System.out.print(rsmd.getColumnName(i)+"\t");
		}
		while(rs.next()){
			System.out.println();
			for(int i=1;i<=numberOfColumns;i++){
				System.out.print(rs.getObject(i)+"\t");//getObject()能够智能匹配字段类型
			}
		}
		ToolDButil.free02(null, cn);
	}
	
	//滚轴的数据修改方式
	public void rollerDate()throws Exception{
		Connection cn=ToolDButil.getConnection();
		Statement st=cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
		String sqlstr="SELECT name,classes,score FROM stuinfo";//表的字段名是否可以用一个数字代替如“1”？
		ResultSet rs=st.executeQuery(sqlstr);

		rs.absolute(3);//第三个
		System.out.println(rs.getObject(2));
		rs.last();//最后一行
		System.out.println(rs.getObject(2));
		rs.first();//第一个
		System.out.println(rs.getObject(2));
		rs.updateString("score", "99");//修改此行
		rs.updateRow();//已经修改了记录
		rs.next();//下一行
		rs.deleteRow();//删除行
		
		ToolDButil.free01(cn, st, rs);
	}
}

JDBC技术学习 https://www.itkc8.com