【JDBC】基本操作

JDBC基本操作

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
//导包注意路径
/*
 * JDBC基本操作
 */
public class JDBCTest {
	public static void main(String[] args) throws ClassNotFoundException, SQLException{
		JDBCTest jdbc = new JDBCTest();
		jdbc.show();
	}
	public void show() throws ClassNotFoundException, SQLException{
		/*
		 * 1.加载驱动类
		 * 2.创建连接对象 Connection
		 * 3.创建statement对象，指示sql语句
		 * 4.执行sql语句
		 * 5.处理操作结果
		 * 6.关闭资源
		 */
		//1.加载驱动类
		Class.forName("com.mysql.jdbc.Driver");
		//—————————————————协议名————ip————————端口号-数据库名
		String url = "jdbc:mysql://localhost:3306/jdbc";//?characterEncoding=utf8
		String user = "root";
		String password = "root";
		
		//2.创建Connection对象
		Connection connection = DriverManager.getConnection(url, user, password);//导Java.sql的包
		
		//3.创建statement对象
		Statement statement = connection.createStatement();//！当执行查询操作时应避免使用Statement。详见关联文章
		
		//4.执行语句--增删改
		String insert = "insert into t_example values(7,'abc')";
		String delete = "delete from t_example where eid = 7";
		String update = "update t_example set ename = 'Jim' where eid = 2";
		int result = statement.executeUpdate(update);
		//4.执行语句--查询
		String select = "select * from t_example";
		ResultSet resultSet = statement.executeQuery(select);
		
		//5.增删改结果处理--通过影响的数据行数判断操作是否成功
		if(result > 0){
			System.out.println("success");
		}else{
			System.out.println("error");
		}
		//5.查询结果处理--格式化输出表
		ResultSetMetaData data = resultSet.getMetaData();//获取元数据
		int columns = data.getColumnCount();//获取元数据中的行数（表字段个数）
		while(resultSet.next()){
			for(int i=1; i<=columns; i++){
				//——————————————————————获取字段名————————————————————获取字段属性
				System.out.print(data.getColumnName(i) +" = "+ resultSet.getObject(i));
				System.out.print("\t");
			}
			System.out.println();
		}
		
		//6.关闭资源
		resultSet.close();
		statement.close();
		connection.close();
	}
}

将基本操作封装成util工具类

package temp;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;


public class DBUtil {
	private static final String URL;
	private static final String USER;
	private static final String PASSWORD;
	private Connection conn = null;
	private PreparedStatement st = null;
	//常量要在静态初始化块中初始化
	static{
		Properties p = new Properties();
		InputStream in = ClassLoader.getSystemResourceAsStream("config.properties");
		try {
			p.load(in);
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		URL = p.getProperty("url");//从配置文件获取相关初始化信息
		USER = p.getProperty("user");
		PASSWORD = p.getProperty("password");
		try {
			in.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private Connection getConnection() throws ClassNotFoundException, SQLException{
		Class.forName("com.mysql.jdbc.Driver");
		return DriverManager.getConnection(URL,USER,PASSWORD);
	}
	
	public boolean update(String sqlCode, Object ... params) throws SQLException, ClassNotFoundException{
		boolean result = false;
		conn = getConnection();
		st = conn.prepareStatement(sqlCode);
		for (int i = 0; i < params.length; i++) {
			st.setObject(i+1, params[i]);//数据库从1开始
		}
		result = st.executeUpdate() > 0;//返回一个boolean
		st.close();
		conn.close();
		return result;
	}
	
	public List<Map<String, Object>> query(String sqlCode, Object ... params) throws SQLException, ClassNotFoundException{
		List<Map<String, Object>> list = new ArrayList<>();
		conn = getConnection();
		st = conn.prepareStatement(sqlCode);
		for (int i = 0; i < params.length; i++) {
			st.setObject(i+1, params[i]);
		}
		ResultSet rs = st.executeQuery();
		//一个map一格数据
		Map<String, Object> map = null;
		//rs的元数据，获取列的名字、数目、类型等数据
		ResultSetMetaData data = rs.getMetaData();
		
		int columns = data.getColumnCount();
		while(rs.next()){
			map = new HashMap<String, Object>();
			//列数自定义
			for(int i=1; i<=columns; i++){
				//———————————获取字段名———————————————获取字段属性
				map.put(data.getColumnName(i), rs.getObject(i));
			}
			list.add(map);
		}
		rs.close();
		st.close();
		conn.close();
		return list;
	}
}

DBUtil工具类测试

package temp;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class Demo {
	public static void main(String[] args) throws ClassNotFoundException, SQLException{
		DBUtil db = new DBUtil();
		List<Map<String, Object>> result = db.query("select * from h_student");

		for (Map<String, Object> map : result) {
			for (String s : map.keySet()) {
				System.out.print(s+"="+map.get(s)+"\t");
			}
			System.out.println();
		}
		/*
		 	测试结果：
		 	stuId=1001	stuCredit=30	stuName=Tom	
			stuId=1002	stuCredit=27	stuName=Jerry	
			stuId=1003	stuCredit=32	stuName=Ami	
			stuId=1004	stuCredit=35	stuName=Elia	
			stuId=1005	stuCredit=36	stuName=Tini	
			stuId=1006	stuCredit=30	stuName=Kitt	
			stuId=1007	stuCredit=29	stuName=Eve	
			stuId=1008	stuCredit=21	stuName=Bob	
			stuId=1009	stuCredit=10	stuName=Nike	
		 */
	}
}

DBUtil类查询方法改进，使用泛型方法：

public<T> List<T> query(Class<T> clazz,String sqlCode, Object ... params) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException, NoSuchFieldException{
		List<T> list = new ArrayList<>();
		conn = getConnection();
		st = conn.prepareStatement(sqlCode);
		for (int i = 0; i < params.length; i++) {
			st.setObject(i+1, params[i]);
		}
		ResultSet rs = st.executeQuery();
		ResultSetMetaData data = rs.getMetaData();
		
		int columns = data.getColumnCount();
		while(rs.next()){
			Constructor<T> con = clazz.getConstructor();
			T t = con.newInstance();
			
			for(int i=1; i<=columns; i++){
				
				Field f = clazz.getDeclaredField(data.getColumnName(i));
				Object obj = rs.getObject(i);
				if(!rs.wasNull()){
					f.setAccessible(true);
					f.set(t, obj);
				}
			}
			list.add(t);
		}
		rs.close();
		st.close();
		conn.close();
		return list;
	}

测试类：

DBUtil db = new DBUtil();
List<T_student> result = db.query(T_student.class,"select * from t_student");

for (T_student t : result) {
	System.out.println(t);
}