Java数据库访问小结

DBHelper类访问数据库，Dao类写数据访问，View类进行应用，初学实例图书管理系统。

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;
	}
}

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 {
	
	private static SqlSessionFactory sessionFactory;
	private static Reader reader;
	private DBHelper(){}
	
	static {
		String resource = "util/config.xml"; 
		//加载mybatis的配置文件（它也加载关联的映射文件）
		try {
			reader = Resources.getResourceAsReader(resource);
		} catch (IOException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} 
	}
	public static SqlSessionFactory getSessionFactory() throws Exception{
		
		//构建sqlSession的工厂
		sessionFactory = new SqlSessionFactoryBuilder().build(reader);
		
		return sessionFactory;
	}
	
	

}

<?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());
	}