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();}@Overridepublic Object rowMap(ResultSet rs) throws Exception {// TODO Auto-generated method stubreturn null;}@Overridepublic List<Object> rowMapList(ResultSet rs) throws Exception {// TODO Auto-generated method stubreturn 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;}@Overridepublic 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());}