首页 > 代码库 > Java数据库访问小结
Java数据库访问小结
1、JDBC访问方法
3、myBatis访问 就是xml文件配置比较烦，用起来舒服些。 实例测试。
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;
}
}
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数据库访问小结
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。