Java資料庫訪問小結

標籤：資料庫   java   mybatis   

1、JDBC存取方法

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

Java資料庫訪問小結