Java操作数据库快捷方法

JDBC必备知识1

	    最近原本是在学Javaweb的,但是由于涉及到了数据库,所以还是要好好复习一下JDBC
	顺便水一波博客.

首先就是数据库的连接与关闭函数,全部整合到一个类中,方便使用

public class JDBCUtils {

	public static Connection getConnection() throws Exception {
		InputStream is =ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
		
		Properties pros = new Properties();
		pros.load(is);
		
		String user = pros.getProperty("user");
		String password = pros.getProperty("password");
		String url = pros.getProperty("url");
		String driverClass = pros.getProperty("driverClass");
		
		Class.forName(driverClass);
		
		Connection conn = DriverManager.getConnection(url,user,password);
		return conn;
	}
	
	public static void closeResource(Connection conn, java.sql.PreparedStatement ps) {
		
		try {
			if(ps != null) {
				ps.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if(conn != null) {
				conn.close();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void closeResource(Connection conn, java.sql.PreparedStatement ps,ResultSet rs)//重载加入结果集 {
		try {
			if(ps != null) {
				ps.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if(conn != null) {
				conn.close();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if(rs != null) {
				rs.close();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
	
}

连接好数据库之后,难免会有一些操作,这里便介绍一下"增删改"的通用方法

public class preparedstatementUpdateTest {
	//update()需传入sql语句以及占位符(可传多个)
	/*@Test
	public void testCommonUpdate() {
		  String sql = "delete from customers where id = ?";
		  update(sql,3);
	}*/

	/*@Test
	public void testCommonUpdate() {
		  //若表名中有关键字,需要用` `("1"左边)来修饰.
		String sql = "update `order` set order_name = ? where order_id = ?";
		update(sql, "DD","2");
	}*/

	public void update(String sql, Object ...args) {//占位符个数与可变形参长度相同

		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JDBCUtils.getConnection();
			ps = conn.prepareStatement(sql);
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			ps.execute();
		} catch (Exception e) {
			
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, ps);
		}
		
		
	}
	
}

除此之外,还要有数据库的查询,同样也需要一个通用的函数:

分为查询单条和多条两种方法

//查询单条记录
public <T> List<T> getForList(Class<T> clazz,String sql, Object...args){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			ps = conn.prepareStatement(sql);
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			
			ArrayList<T> list = new ArrayList<T>();
			while(rs.next()) {
				T t = clazz.newInstance();
				for(int i = 0; i < columnCount; i++) {
					Object object = rs.getObject(i + 1);
					
					String columnLabelName = rsmd.getColumnLabel(i + 1);
					
					Field field = clazz.getDeclaredField(columnLabelName);
					field.setAccessible(true);
					field.set(t, object);
				}
				list.add(t);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, ps, rs);
		}
		return null;
	}
	
	//查询多条记录,返回值为一个链表
	public <T> T getInstance(Class<T> clazz,String sql, Object...args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			ps = conn.prepareStatement(sql);
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			//获取结果集
			rs = ps.executeQuery();
			//获取结果集的元数据
			ResultSetMetaData rsmd = rs.getMetaData();
			//获取列数
			int columnCount = rsmd.getColumnCount();
			if(rs.next()) {
				T t = clazz.newInstance();
				for(int i = 0; i < columnCount; i++) {
				 	//获取每个列的列值
					Object object = rs.getObject(i + 1);
					//获取每个列的列名
					String columnLabelName = rsmd.getColumnLabel(i + 1);
					//反射将对象指定名属性赋指定值
					Field field = clazz.getDeclaredField(columnLabelName);
					field.setAccessible(true);
					field.set(t, object);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, ps, rs);
		}
		return null;
	}

有了这三个方法,就可以基本操作数据库了,快连上你的数据库的,耍起来,giao!