JDBC基本操作

连接数据库的工具类

DbUtil

public class DbUtil {

	// 数据库地址
	private static String dbUrl="jdbc:mysql://localhost:3306/db_bank";
	// 用户名
	private static String dbUserName="root";
	// 密码
	private static String dbPassword="123456";
	// 驱动名称
	private static String jdbcName="com.mysql.jdbc.Driver";
	
	/**
	 * 获取数据库连接
	 * @return
	 * @throws Exception
	 */
	public Connection getCon()throws Exception{
		Class.forName(jdbcName);
		Connection con=DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
		return con;
	}
	
	/**
	 * 关闭连接
	 * @param con
	 * @throws Exception
	 */
	public void close(Statement stmt,Connection con)throws Exception{
		if(stmt!=null){
			stmt.close();
			if(con!=null){
				con.close();
			}
		}
	}
	
	/**
	 * 关闭连接
	 * @param con
	 * @throws Exception
	 */
	public void close(PreparedStatement pstmt,Connection con)throws Exception{
		if(pstmt!=null){
			pstmt.close();
			if(con!=null){
				con.close();
			}
		}
	}
	
	/**
	 * 关闭连接
	 * @param con
	 * @throws Exception
	 */
	public void close(CallableStatement cstmt,Connection con)throws Exception{
		if(cstmt!=null){
			cstmt.close();
			if(con!=null){
				con.close();
			}
		}
	}
}

Statement接口实现Insert，update，delete

实现Insert

	public static void main(String[] args) throws Exception{
		DbUtil dbUtil=new DbUtil();
		String sql="insert into t_user values(null,'xxxx',888,'B',1)";
		Connection con=dbUtil.getCon(); //获取数据连接
		Statement stmt=con.createStatement(); // 获取Statement
		int result=stmt.executeUpdate(sql);
		System.out.println("操作的结果："+result+"数据");
		stmt.close();  // 关闭statement
		con.close();   // 关闭连接
	}

实现update

private static DbUtil dbUtil = new DbUtil();
	private static int updateBook(Book book) throws Exception {
		Connection con = dbUtil.getCon(); // 获取连接
		String sql = "update t_book set bookName='" + book.getBookName()
				+ "',price=" + book.getPrice() + ",author='" + book.getAuthor()
				+ "',bookTypeId=" + book.getBookTypeId() + " where id="
				+ book.getId();  
		Statement stmt = con.createStatement(); // 创建Statement
		int result = stmt.executeUpdate(sql);
		dbUtil.close(stmt, con); // 关闭Statement和连接
		return result;
	}

	public static void main(String[] args) throws Exception{
		Book book=new Book(3,"2222", 121, "222", 1);
		int result=updateBook(book);
		if(result==1){
			System.out.println("更新成功！");
		}else{
			System.out.println("更新败！");
		}
		
	}

实现删除

	private static DbUtil dbUtil=new DbUtil();
	private static int deleteBook(int id)throws Exception{
		Connection con = dbUtil.getCon(); // 获取连接
		String sql ="delete from t_book where id="+id;
		Statement stmt = con.createStatement(); // 创建Statement
		int result = stmt.executeUpdate(sql);
		dbUtil.close(stmt, con); // 关闭Statement和连接
		return result;
	}
	
	public static void main(String[] args) throws Exception{
		int result=deleteBook(3);
		if(result==1){
			System.out.println("删除成功！");
		}else{
			System.out.println("删除失败！");
		}
	}

PreparedStatement 实现预处理操作，现在数据库中准备一条sql，但sql具体内容不设置，而是之后再进行设置

实现Insert

	private static DbUtil dbUtil=new DbUtil();
	private static int addBook(Book book)throws Exception{
		Connection con=dbUtil.getCon(); // 获取连接
		String sql="insert into t_book values(null,?,?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());  // 给第一个坑设置值
		pstmt.setFloat(2, book.getPrice());  // 给第二个坑设置值
		pstmt.setString(3, book.getAuthor()); // 给第三个坑设置值
		pstmt.setInt(4, book.getBookTypeId());  // 给第四个坑设置值
		int result=pstmt.executeUpdate();
		dbUtil.close(pstmt, con);
		return result;
	}
	
	public static void main(String[] args) throws Exception{
		Book book=new Book("xxxx", 1, "xxx", 1);
		int result=addBook(book);
		if(result==1){
			System.out.println("添加成功！");
		}else{
			System.out.println("添加失败！");
		}
	}

实现update

	private static DbUtil dbUtil=new DbUtil();
	private static int updateBook(Book book)throws Exception{
		Connection con=dbUtil.getCon();
		String sql="update t_book set bookName=?,price=?,author=?,bookTypeId=? where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setFloat(2, book.getPrice());
		pstmt.setString(3, book.getAuthor());
		pstmt.setInt(4, book.getBookTypeId());
		pstmt.setInt(5, book.getId());
		int result=pstmt.executeUpdate();
		dbUtil.close(pstmt, con);
		return result;
	}
	
	public static void main(String[] args) throws Exception{
		Book book=new Book(12,"K2", 2, "K", 2);
		int result=updateBook(book);
		if(result==1){
			System.out.println("更新成功！");
		}else{
			System.out.println("更新失败！");
		}
	}

实现删除

	private static DbUtil dbUtil=new DbUtil();
	private static int deleteBook(int id)throws Exception{
		Connection con=dbUtil.getCon();
		String sql="delete from t_book where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setInt(1, id);
		int result=pstmt.executeUpdate();
		dbUtil.close(pstmt, con);
		return result;
	}
	
	public static void main(String[] args)throws Exception {
		int result=deleteBook(12);
		if(result==1){
			System.out.println("删除成功！");
		}else{
			System.out.println("删除失败！");
		}
	}
}

JDCB查询返回的是二维的结果集，用RessultSet来遍历结果集，获取每一行的数据

getInt() getString的名字为数据库字段名称

	private static List listBook3()throws Exception{
		List bookList=new ArrayList(); 
		Connection con = dbUtil.getCon(); // 获取连接
		String sql = "select * from t_book";
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery(); // 返回结果集ResultSet
		while (rs.next()) {
			int id = rs.getInt("id"); // 获取第一个列的值 编号id
			String bookName = rs.getString("bookName"); // 获取第二个列的值 图书名称 bookName
			float price = rs.getFloat("price"); // 获取第三列的值 图书价格 price
			String author = rs.getString("author"); // 获取第四列的值 图书作者 author
			int bookTypeId = rs.getInt("bookTypeId"); // 获取第五列的值 图书类别id
			Book book=new Book(id, bookName, price, author, bookTypeId);
			bookList.add(book);
		}
		return bookList;
	}

	public static void main(String[] args) throws Exception {
		List bookList=listBook3();
		for (Book book : bookList) {
			System.out.println(book);
		}
	}

插入CLOB大数据

定义流

public class Book {

	private int id;
	private String bookName;
	private float price;
	private String author;
	private int bookTypeId;
	private File context;
	private File pic;
	
	private static int addBook(Book book)throws Exception{
		Connection con=dbUtil.getCon(); // 获取连接
		String sql="insert into t_book values(null,?,?,?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());  // 给第一个坑设置值
		pstmt.setFloat(2, book.getPrice());  // 给第二个坑设置值
		pstmt.setString(3, book.getAuthor()); // 给第三个坑设置值
		pstmt.setInt(4, book.getBookTypeId());  // 给第四个坑设置值
		File context=book.getContext(); // 获取文件
		InputStream inputStream=new FileInputStream(context);
		pstmt.setAsciiStream(5, inputStream,context.length());  // 给第五个坑设置值
		int result=pstmt.executeUpdate();
		dbUtil.close(pstmt, con);
		return result;
	}
	
	public static void getBook(int id)throws Exception{
		Connection con=dbUtil.getCon();
		String sql="select * from t_book where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			String bookName=rs.getString("bookName");
			float price=rs.getFloat("price");
			String author=rs.getString("author");
			int bookTypeId=rs.getInt("bookTypeId");
			Clob c=rs.getClob("context");
			String context=c.getSubString(1, (int)c.length());
			System.out.println("图书名称："+bookName);
			System.out.println("图书价格:"+price);
			System.out.println("图书作者："+author);
			System.out.println("图书类型ID："+bookTypeId);
			System.out.println("图书内容："+context);
		}
		dbUtil.close(pstmt, con);
	}

插入BLOB 图片

	private static int addBook(Book book)throws Exception{
		Connection con=dbUtil.getCon(); // 获取连接
		String sql="insert into t_book values(null,?,?,?,?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());  // 给第一个坑设置值
		pstmt.setFloat(2, book.getPrice());  // 给第二个坑设置值
		pstmt.setString(3, book.getAuthor()); // 给第三个坑设置值
		pstmt.setInt(4, book.getBookTypeId());  // 给第四个坑设置值
		File context=book.getContext(); // 获取文件
		InputStream inputStream=new FileInputStream(context);
		pstmt.setAsciiStream(5, inputStream,context.length());  // 给第五个坑设置值
		
		File pic=book.getPic(); // 获取图片文件
		InputStream inputStream2=new FileInputStream(pic);
		pstmt.setBinaryStream(6, inputStream2, pic.length()); // 给第六个坑设置值
		int result=pstmt.executeUpdate();
		dbUtil.close(pstmt, con);
		return result;
	}
	
	public static void getBook(int id)throws Exception{
		Connection con=dbUtil.getCon();
		String sql="select * from t_book where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			String bookName=rs.getString("bookName");
			float price=rs.getFloat("price");
			String author=rs.getString("author");
			int bookTypeId=rs.getInt("bookTypeId");
			Clob c=rs.getClob("context");
			String context=c.getSubString(1, (int)c.length());
			Blob b=rs.getBlob("pic");
			FileOutputStream out=new FileOutputStream(new File("d:/pic2.jpg"));
			out.write(b.getBytes(1, (int)b.length()));
			out.close();
			System.out.println("图书名称："+bookName);
			System.out.println("图书价格:"+price);
			System.out.println("图书作者："+author);
			System.out.println("图书类型ID："+bookTypeId);
			System.out.println("图书内容："+context);
		}
		dbUtil.close(pstmt, con);
	}

使用CallableStatement调用存储过程

	private static DbUtil dbUtil=new DbUtil();
	private static String getBookNameById(int id)throws Exception{
		Connection con=dbUtil.getCon();  // 获取数据库连接
		String sql="{CALL pro_getBookNameById(?,?)}";
		CallableStatement cstmt=con.prepareCall(sql);
		cstmt.setInt(1, id); // 设置第一个参数
		cstmt.registerOutParameter(2, Types.VARCHAR);  // 设置返回类型
		cstmt.execute();
		String bookName=cstmt.getString("bN");  // 获取返回值
		dbUtil.close(cstmt, con);
		return bookName;
	}
	
	public static void main(String[] args) throws Exception{
		System.out.println("图书名称是："+getBookNameById(11));
	}

DatabaseMetaData获取数据库基本信息

	public static void main(String[] args)throws Exception {
		DbUtil dbUtil=new DbUtil();
		Connection con=dbUtil.getCon();
		DatabaseMetaData dmd=con.getMetaData(); // 获取元数据
		System.out.println("数据库名称："+dmd.getDatabaseProductName());
		System.out.println("数据库版本："+dmd.getDriverMajorVersion()+"."+dmd.getDriverMinorVersion());
		
	}

使用ResultSetMetaData获取ResultSet对象中的信息

	public static void main(String[] args) throws Exception{
		DbUtil dbUtil=new DbUtil();
		Connection con=dbUtil.getCon();
		String sql="select * from t_book";
		PreparedStatement pstmt=con.prepareStatement(sql);
		ResultSetMetaData rsmd=pstmt.getMetaData();
		int num=rsmd.getColumnCount(); // 获取元数据列的总数
		System.out.println("共有"+num+"列");
		for(int i=1;i