Java数据库訪问小结

</pre>1、JDBC訪问方法</p><p></p><p>DBHelper类訪问数据库。Dao类写数据訪问，View类进行应用，初学实例图书管理系统。</p><p></p><pre class="java" name="code">package util;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBHelper {
	private static Connection conn;
	private static final String DBurl="jdbc:mysql://localhost:3306/db_book?

useUnicode=true&characterEncoding=UTF-8"; private static final String DBuser="root"; private static final String DBpass="root"; private static final String DRIVER="com.mysql.jdbc.Driver"; static { try { Class.forName(DRIVER); } catch (ClassNotFoundException e) { // TODO 自己主动生成的 catch 块 e.printStackTrace(); } } private DBHelper() { } public static Connection getConnection() throws Exception { if(conn==null) { conn=DriverManager.getConnection(DBurl, DBuser, DBpass); } return conn; } public static void closeConn()throws Exception { if(conn!=null) { conn.close(); } } }

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import util.StrUtil;
import model.Book;


public class BookDao {
	
	public int addBook(Connection conn,Book bk) throws Exception
	{
		String sql="insert into t_book values(null,?,?,?,?,?,?)";
		PreparedStatement psmt=conn.prepareStatement(sql);
		psmt.setString(1, bk.getBookname());
		psmt.setString(2, bk.getAuthor());
		psmt.setString(3, bk.getSex());
		psmt.setString(4, bk.getPublisher());
		psmt.setString(5, bk.getBookdes());
		psmt.setInt(6, bk.getBooktypeid());
		return psmt.executeUpdate();		
	}

	public int delBook(Connection conn,Book bk) throws Exception
	{
		String sql="delete from t_book where id ='"+bk.getId() +"'";
		PreparedStatement psmt=conn.prepareStatement(sql);
		return psmt.executeUpdate();		
	}
	
	public int bookModify(Connection con,Book bk)throws Exception{
		String sql="update t_booktype set booktypename=?,booktypedes=?

where id=?"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setString(1, bk.getBookname()); pstmt.setString(2, bk.getBookdes()); pstmt.setInt(3, bk.getId()); return pstmt.executeUpdate(); } public ResultSet bookList(Connection con,Book book)throws Exception{ StringBuffer sb=new StringBuffer("SELECT t_book.id,t_book.bookname,t_book.author,t_book.sex,t_book.publisher,t_book.bookdes,t_booktype.booktypename FROM t_book,t_booktype WHERE t_book.booktypeid=t_booktype.id"); if(StrUtil.isNotEmpty(book.getBookname())){ sb.append(" and bookname like '%"+book.getBookname()+"%'"); } if(StrUtil.isNotEmpty(book.getAuthor())){ sb.append(" and author like '%"+book.getAuthor()+"%'"); } if(StrUtil.isNotEmpty(book.getSex())){ sb.append(" and sex = '"+book.getSex()+"'"); } if(book.getBooktypeid()!=-1){ sb.append(" and booktypeid = "+book.getBooktypeid()); } PreparedStatement pstmt=con.prepareStatement(sb.toString()); return pstmt.executeQuery(); } public ResultSet bookListAll(Connection con,Book book)throws Exception{ StringBuffer sb=new StringBuffer("SELECT t_book.id,t_book.bookname,t_book.author,t_book.sex,t_book.publisher,t_book.bookdes,t_booktype.booktypename FROM t_book,t_booktype WHERE t_book.booktypeid=t_booktype.id"); PreparedStatement pstmt=con.prepareStatement(sb.toString()); return pstmt.executeQuery(); } public boolean getBookByBookTypeId(Connection con,String bookTypeId)throws Exception{ String sql="select * from t_book where booktypeid=?"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setString(1, bookTypeId); ResultSet rs=pstmt.executeQuery(); return rs.next(); } }

2、依旧是JDBC方法。Dao类採用简单模版方法   练手实例 源码管理系统

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import util.DBHelper;

interface RowMapImpl {
	abstract Object rowMap(ResultSet rs) throws Exception;

	abstract List<Object> rowMapList(ResultSet rs) throws Exception;
}

public class BaseDao implements RowMapImpl {
	public Object query(String sql, Object[] args, RowMapImpl rowMapImpl)
			throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		conn = DBHelper.getConnection();
		ps = conn.prepareStatement(sql);
		for (int i = 0; i < args.length; i++)
			ps.setObject(i + 1, args[i]);
		rs = ps.executeQuery();
		Object obj = null;
		if (rs.next()) {
			obj = rowMapImpl.rowMap(rs);
		}
		return obj;
	}

	public List<Object> queryList(String sql, Object[] args,
			RowMapImpl rowMapImpl) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Object> list = null;
		conn = DBHelper.getConnection();
		ps = conn.prepareStatement(sql);
		for (int i = 0; i < args.length; i++)
			ps.setObject(i + 1, args[i]);
		rs = ps.executeQuery();
		list = new ArrayList<Object>();
		list = rowMapImpl.rowMapList(rs);
		return list;
	}

	public int operate(String sql, Object[] args) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		conn = DBHelper.getConnection();
		ps = conn.prepareStatement(sql);
		for (int i = 0; i < args.length; i++)
			ps.setObject(i + 1, args[i]);
		return ps.executeUpdate();
	}

	@Override
	public Object rowMap(ResultSet rs) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Object> rowMapList(ResultSet rs) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

}

package dao;

import java.sql.ResultSet;
import java.util.List;

import model.Content;

public class ContentDao {
	private BaseDao template = new BaseDao();
	public int addTree(Content cont) throws Exception {
		String sql = "insert into t_content values(?

,?,?)"; Object[] args = new Object[] { cont.getNodeId(), cont.getContent(), cont.getUpdateTime() }; return template.operate(sql, args); } public int delTree(Content cont) throws Exception { String sql = "delete from t_content where NodeId=?"; Object[] args = new Object[] { cont.getNodeId() }; return template.operate(sql, args); } public int updateTree(Content cont) throws Exception { String sql = "update t_content set NodeId=?

, Content=? UpdateTime=? "; Object[] args = new Object[] { cont.getNodeId(), cont.getContent(), cont.getUpdateTime() }; return template.operate(sql, args); } public Content findTree(String NodeId) throws Exception { String sql = "select * from t_content where NodeId=?

"; Object[] args = new Object[] { NodeId }; Object cont = template.query(sql, args, new RowMapImpl() { public Object rowMap(ResultSet rs) throws Exception { Content cont = new Content(); cont.setNodeId(rs.getInt("NodeId")); cont.setContent(rs.getString("Content")); cont.setUpdateTime(rs.getString("UpdateTime")); return cont; } @Override public List<Object> rowMapList(ResultSet rs) throws Exception { // TODO 自己主动生成的方法存根 return null; } }); return (Content) cont; } }

3、myBatis訪问  就是xml文件配置比較烦，用起来舒服些。  实例測试。

package util;
import java.io.IOException;
import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class DBHelper {
	
<p>
 private static SqlSessionFactory sessionFactory;
 private static Reader reader;
 private DBHelper(){}</p><p> public static SqlSessionFactory getSessionFactory() throws Exception{
  
  String resource = "util/config.xml"; 
  //载入mybatis的配置文件（它也载入关联的映射文件）
  try {
   reader = Resources.getResourceAsReader(resource);
  } catch (IOException e) {   
   e.printStackTrace();
  } 
  //构建sqlSession的工厂
  sessionFactory = new SqlSessionFactoryBuilder().build(reader);
  
  return sessionFactory;</p>	

}

<?xml version="1.0" encoding="UTF-8" ?

> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="dao.UserDao"> <select id="getUser" parameterType="int" resultType="User"> select * from t_user where id=#{id} </select> <select id="getAllUser" resultType="User"> select * from t_user </select> <delete id="deleteUser" parameterType="int" > delete from t_user where id=#{id} </delete> <update id="updateUser" parameterType="User"> update t_user set username=#{username}, password=#{password} where id=#{id} </update> <insert id="insertUser" parameterType="User"> insert into t_user(username,password) values(#{username},#{password}) </insert> </mapper>

package dao;

import java.util.List;

import model.User;

public interface UserDao {

	public User getUser(int i);
	
	public List<User> getAllUser();
	
	public int insertUser(User u);
	
	public int updateUser(User u);
	
	public int deleteUser(int i);
	
}

public static void main(String[] args) throws Exception {
	    SqlSession session=DBHelper.getSessionFactory().openSession(true);
	    UserDao userDao=session.getMapper(UserDao.class);
		User user=userDao.getUser(1);
		System.out.println(user.getUsername());
	}