Java代码实现 增删查 + 分页——实习第四天
今天项目内容已经开始了,并且已经完成好多基本操作,今天就开始总结今天学习到的内容,和我遇到的问题,以及分析这其中的原因。
内容模块:
1:Java代码实现对数据库的增删查;
2:分页且获取页面信息;
这里针对于项目里面的Genre实体,以及对于它的操作进行举例
1 package com.music.entity; 2 3 public class Genre { 4 private int id; 5 private String name; 6 private String description; 7 8 public int getId() { 9 return id; 10 } 11 public void setId(int id) { 12 this.id = id; 13 } 14 public String getName() { 15 return name; 16 } 17 public void setName(String name) { 18 this.name = name; 19 } 20 public String getDescription() { 21 return description; 22 } 23 public void setDescription(String description) { 24 this.description = description; 25 } 26 27 }
逻辑层代码展示:
GenreDao:
1 package com.music.Dao; 2 3 import java.util.List; 4 5 import com.music.entity.Genre; 6 7 public interface GenreDao { 8 //查询 9 public List<Genre> getAll(); 10 //删除 11 public boolean deleteGenre(int id); 12 //插入 13 public boolean addGenre(Genre g); 14 //更新 15 public boolean updateGenre(Genre g); 16 }
在这个接口里方法的具体实现GenreDaoImpl:
1 package com.music.Dao.Impl; 2 3 import java.sql.ResultSet; 4 import java.sql.SQLException; 5 import java.util.ArrayList; 6 import java.util.List; 7 8 import com.music.Dao.GenreDao; 9 import com.music.entity.Genre; 10 11 public class GenreDaoImpl extends BaseDao implements GenreDao{ 12 13 //保存获取结果 14 ArrayList<Genre> genres = new ArrayList<Genre>(); 15 @Override 16 public List<Genre> getAll() { 17 try { 18 //创建连接 19 openConnection(); 20 String sql = "select * from genre"; 21 //执行查询,获取结果 22 ResultSet resultSet = executeQuery(sql, null); 23 //将查询结果转换成对象 24 while (resultSet.next()) { 25 Genre g = new Genre(); 26 g.setId(resultSet.getInt("id")); 27 g.setName(resultSet.getString("name")); 28 g.setDescription(resultSet.getString("description")); 29 genres.add(g); 30 } 31 } catch (ClassNotFoundException e) { 32 33 e.printStackTrace(); 34 } catch (SQLException e) { 35 36 e.printStackTrace(); 37 }finally{ 38 closeResourse(); 39 } 40 return genres; 41 } 42 43 @Override 44 public boolean deleteGenre(int id) { 45 boolean result = false; 46 try { 47 openConnection(); 48 String sql ="delete from genre where id = ?"; 49 result = excute(sql, new Object[]{id}); 50 } catch (ClassNotFoundException e) { 51 52 e.printStackTrace(); 53 } catch (SQLException e) { 54 55 e.printStackTrace(); 56 }finally{ 57 closeResourse(); 58 } 59 return result; 60 } 61 62 @Override 63 public boolean addGenre(Genre g) { 64 boolean result = false; 65 try { 66 openConnection(); 67 String sql ="insert into genre value(?,?,?)"; 68 result =excute(sql, new Object[]{ 69 g.getId(), 70 g.getDescription(), 71 g.getName() 72 }); 73 } catch (ClassNotFoundException e) { 74 e.printStackTrace(); 75 } catch (SQLException e) { 76 e.printStackTrace(); 77 }finally{ 78 closeResourse(); 79 } 80 return result; 81 } 82 83 @Override 84 public boolean updateGenre(Genre g) { 85 boolean result = false; 86 try { 87 openConnection(); 88 String sql = "update genre set name = ?, description =? where id=?"; 89 result = excute(sql, new Object[]{ 90 g.getId() 91 }); 92 } catch (ClassNotFoundException e) { 93 94 e.printStackTrace(); 95 } catch (SQLException e) { 96 97 e.printStackTrace(); 98 }finally{ 99 closeResourse(); 100 } 101 return result; 102 103 } 104 105 public static void main(String[] args) { 106 GenreDaoImpl genreDaoImpl = new GenreDaoImpl(); 107 genreDaoImpl.getAll(); 108 System.out.println(genreDaoImpl); 109 110 } 111 113 }
这里还必须要提出BaseDao:
1 package com.music.Dao.Impl; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.PreparedStatement; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 9 public class BaseDao { 10 //连接数据库 11 private String className = "com.mysql.jdbc.Driver"; 12 private String dburl = "jdbc:mysql://localhost/ZJJ"; 13 private String user = "root"; 14 private String password = "root"; 15 private Connection connection; 16 private PreparedStatement statement; 17 private ResultSet resultSet; 18 19 public void openConnection() throws ClassNotFoundException, SQLException{ 20 //加载驱动 21 Class.forName(className); 22 //创建连接 23 connection = DriverManager.getConnection(dburl,user,password); 24 } 25 26 //查询方法 27 public ResultSet executeQuery(String sql,Object[] params) throws SQLException{ 28 statement =connection.prepareStatement(sql); 29 //追加参数 30 if(params !=null){ 31 int i=1; 32 for (Object object : params) { 33 statement.setObject(i, object); 34 i++; 35 } 36 } 37 resultSet =statement.executeQuery(); 38 return resultSet; 39 } 40 41 //更新 42 public boolean excute(String sql,Object[] params) throws SQLException { 43 statement =connection.prepareStatement(sql); 44 if(params !=null){ 45 int i=1; 46 for (Object object : params) { 47 statement.setObject(i, object); 48 } 49 } 50 return statement.execute(); 51 } 52 //释放资源 53 public void closeResourse(){ 54 try { 55 if(resultSet != null){ 56 resultSet.close(); 57 } 58 if(statement != null){ 59 statement.close(); 60 } 61 if(connection != null){ 62 connection.close(); 63 } 64 65 } catch (SQLException e) { 66 // TODO Auto-generated catch block 67 e.printStackTrace(); 68 } 69 } 70 71 72 73 }
这个里面的方法都会获得调用。
今天报了一个错误:
错误的原因是:
这个setObject(i,object)有一个好处就是将所有的对象类型都写成object,这样就可以不用分开写各个类型的方法,比较简便。
分页:
接口部分:
1 //分页 2 public List<Album> getAlbumWithPage(int genreid,int pageNum,int pageSize);
1 1 public List<Album> getAlbumWithPage(int genreid, int pageNum, int pageSize)
实现部分:
1 public List<Album> getAlbumWithPage(int genreid, int pageNum, int pageSize) { 2 ArrayList<Album> albums = new ArrayList<Album>(); 3 //pageNum当前页数 4 try { 5 openConnection(); 6 String sql= "select * from album where genreid =? limit ?,?"; 7 ResultSet resultSet = executeQuery(sql, new Object[]{ 8 genreid, 9 (pageNum-1)*pageSize, 10 pageSize 11 }); 12 while (resultSet.next()) { 13 Album al= new Album(); 14 al.setId(resultSet.getInt("id")); 15 al.setGenreid(resultSet.getInt("genreid")); 16 al.setArtist(resultSet.getString("artist")); 17 al.setTitle(resultSet.getString("title")); 18 al.setPrice(resultSet.getBigDecimal("price")); 19 al.setStock(resultSet.getInt("stock")); 20 al.setDateReleased(resultSet.getString("dateReleased")); 21 al.setDescription(resultSet.getString("description")); 22 albums.add(al); 23 } 24 } catch (ClassNotFoundException e) { 25 26 e.printStackTrace(); 27 } catch (SQLException e) { 28 29 e.printStackTrace(); 30 } 31 return albums;
1 public List<Album> getAlbumWithPage(int genreid, int pageNum, int pageSize) {
return daoImpl.getAlbumWithPage(genreid, pageNum, pageSize); 4 } 5 6 @Override 7 public int getRowCountWithGenreid(int id) { 8 9 return daoImpl.getAlbumWithGenreid(id).size(); 10 }
JSP代码部分
1 <%@page import="com.music.entity.Album"%> 2 <%@page import="com.music.biz.Impl.AlbumBizImpl"%> 3 <%@page import="com.music.biz.AlbumBiz"%> 4 <%@page import="com.music.Dao.Impl.AlbumDaoImpl"%> 5 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> 6 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 7 <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> 8 9 <html> 10 <head> 11 <title>欢迎光临 Music Store</title> 12 <link type="text/css" rel="Stylesheet" href="style/front.css"/> 13 <script type="text/javascript" src="script/jquery-1.4.1.js"></script> 14 </head> 15 <% 16 AlbumBiz albumItem = new AlbumBizImpl(); 17 String id = request.getParameter("genreId"); 18 19 int pageSize =3; 20 int pageNum =1; 21 22 if(request.getParameter("page")!=null){ 23 pageNum =Integer.valueOf(request.getParameter("page")); 24 } 25 26 List<Album> albums = albumItem.getAlbumWithPage(Integer.valueOf(id), pageNum, pageSize); 27 28 request.setAttribute("albums", albums); 29 request.setAttribute("pageNum", pageNum); 30 31 int rows = albumItem.getRowCountWithGenreid(Integer.valueOf(id)); 32 int pageCount = (int)Math.ceil((double)rows/pageSize); 33 // int pageCount =albumItem.getRowCountWithGenreid(Integer.valueOf(id)); 34 request.setAttribute("pageCount", pageCount); 35 36 request.setAttribute("genreId", id); 37 38 39 %> 40 41 <body> 42 <div id="wrapper"> 43 <%@ include file="shared/front_header.jsp" %> 44 <div id="content"> 45 <%@ include file="shared/front_sidebar.jsp" %> 46 <div id="main"> 47 <h3 id="main-title">唱片列表</h3> 48 <c:forEach var="album" items="${albums}"> 49 <table class="albumItem"> 50 <tr> 51 <td rowspan="3" class="albumItem-image"><img src="CoverImages/${album.id}.jpg" alt="" /></td> 52 <td colspan="2" class="albumItem-title"> 53 <a href="album.jsp?albumId=${album.id}">${album.title}</a> 54 </td> 55 </tr> 56 <tr> 57 <td class="albumItem-artist"><strong>歌手:${album.artist }</strong></td> 58 <td class=".albumItem-price"><strong>定价:${album.price }</strong>¥</td> 59 </tr> 60 <tr> 61 <td colspan="2"> 62 ${album.description} 63 </td> 64 </tr> 65 </table> 66 </c:forEach> 67 <hr/> 68 69 <a href="album_list.jsp?page=1&genreId=${genreId}&title=${title}">第一页</a> 70 <c:if test="${pageNum>1 }"> 71 <a href="album_list.jsp?page=${pageNum-1}&genreId=${genreId}&title=${title}">上一页</a> 72 73 </c:if> 74 <c:if test="${pageNum<pageCount}"> 75 <a href="album_list.jsp?page=${pageNum+1}&genreId=${genreId}&title=${title}">下一页</a> 76 </c:if> 77 <a href="album_list.jsp?page=${pageCount}&genreId=${genreId}&title=${title}">最后一页</a> 78 共${pageCount}页,第${pageNum}页。 79 80 </div> 81 <div class="clearBoth"></div> 82 </div> 83 <%@ include file="shared/front_footer.jsp" %> 84 </div> 85 </body> 86 </html>
对于分页,前面的博客有讲述,就不赘述了~
说实话,今天我好累了~就写那么多吧~现在距离下课还有5分钟,我要记会儿单词~
每天都要进步一点,积少以成多。。。