1.SQL语句异常
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘bname like ‘%圣墟%’’ at line 1
“select * from t_mvc_book where trueand bname like ‘%圣墟%’ “
在拼接sql语句要注意空格
2.java异常
Unhandled exception type……
这里我截不出这样的图,因此借了一张网图,类似于
*解决:**只需把有这种异常的语句放在 try…catch… 语句中就行了,如图红色标识处
通用分页需要学习的mysql
– 1.根据条件模糊查询
select * from t_mvc_book where bname like ‘%斗破%’;
– 2.符合条件的记录数
select count(1) from (select * from t_mvc_book where bname like ‘%斗破%’) t;
–3.分页的查询语句
select * from t_mvc_book where bname like ‘%斗破%’ limit start,offset–
start 查询数据集的起始下标
offset:查询展示在当前页的记录
为什么要学、用通用分页
如图
思路
1、将原有的查询向上抽取
2、让返回值变成泛型
3、使用回调函数处理resultset
4、利用反射处理回调函数
5、获取总记录数(页面展示,计算总页数)
6、拼接分页sql语句,获取对应的结果集
导包
连接数据库(点我查看mysql的jdbc连接)
1、代码演示
1.PageBean
package com.wxm.dao;
/**
* 分页代码类
* * @author 2019071003 * */
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List; import com.util.DBAccess;
import com.util.PageBean; public class BaseDao<T> { /**
*
* @param sql 查询不同的实体类,那么对应的sql不同,所以需要传递
* * @param clz 生产出不同的实体类对应的实例,然后装进list容器中返回
* @param pageBean决定是否分页
* @return
* @throws SQLException
* @throws IllegalAccessException
* @throws InstantiationException */
public List<T> executeQuery(String sql,Class clz,PageBean pageBean) throws SQLException, InstantiationException, IllegalAccessException{ Connection con=DBAccess.getConnection(); PreparedStatement ps=null;
ResultSet rs=null;
if(pageBean!=null&&pageBean.isPageination()) { //需要分页
//3.1算符合条件的总记录数 String countSql=getCountSql(sql); ps=(PreparedStatement) con.prepareStatement(countSql); rs=ps.executeQuery();
if(rs.next()) { //算总记录数 pageBean.setTotal(rs.getInt(1));// 3.2 查询出符合条件的结果集 String setsql=getsetsql(sql,pageBean); ps=con.prepareStatement(setsql); rs=ps.executeQuery();
} }else { ps=(PreparedStatement) con.prepareStatement(sql); rs=ps.executeQuery();
}
List<T> list=new ArrayList<>();
T t; while(rs.next()) { //1.实例化一个对象(该对象是空的,里面的属性没有值); //2.去book的所有属性,然后给属性赋值// 2.1获取所有属性对象// 2.2给属性对象赋值
t=(T) clz.newInstance();
Field[] fields = clz.getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true); //注意?:这样去给属性赋值,就要保证实体类的属性名要跟数据库的列名一致
field.set(t, rs.getObject(field.getName())); } list.add(t); } DBAccess.close(con,ps,rs); return list; } private String getsetsql(String sql, PageBean pageBean) { // 利用原生sql拼接出符合条件的结果集的查询sql
return sql+" limit "+ pageBean.getStartIndex()+","+pageBean.getRows(); }
private String getCountSql(String sql) { //获取符合条件的总记录数的sql语句 return "select count(*) from ("+sql+")t"; }
}
2.book
package com.wxm.entity; public class Book { public static final Class clz = null;private int bid; private String bname;
private float price;public int getBid() {
return bid;
}
public void setBid(int bid) {
this.bid = bid;
}
public String getBname() {
return bname;}public void setBname(String bname) {
this.bname = bname;
}public float getPrice() {
return price;
}public void setPrice(float price) { this.price = price;
}
public Book(int bid, String bname, float price) { super();
this.bid = bid;
this.bname = bname;
this.price = price;
}
public Book() {
super();
}
@Override
public String toString() {
return "Book [bid=" + bid + ", bname=" + bname + ", price=" + price + "]";
}
}
3.BookDao
package com.wxm.dao; import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List; import com.wxm.entity.Book;import com.wxm.util.DBAccess;import com.wxm.util.PageBean;import com.wxm.util.StringUtils;
public class BookDao { /** * 第一个版本(mvc完成书籍查询) * @param book * @param pageBean * @return * @throws SQLException */
public List<Book> list(Book book,PageBean pageBean) throws SQLException{
String sql="select * from t_mvc_book where true";
List<Book> list=new ArrayList<>();
String bname=book.getBname();
if(StringUtils.isNotBlank(bname) ) { sql +=" and bname like '%"+bname+"%' "; } // System.out.println(sql); Connection con= DBAccess.getConnection(); PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs=ps.executeQuery();// System.out.println("-----"+rs.next()); while(rs.next()) { list.add(new Book(rs.getLong("bid"), rs.getString("bname"), rs.getFloat("price"))); } return list; // }//
public static void main(String[] args) { BookDao bookDao=new BookDao(); Book book=new Book();
book.setBname("圣墟"); try {
List<Book> list = bookDao.list(book, null);
System.out.println(list.size());
for (Book b : list) {
System.out.println(b);
}
} catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace();
}
}}
4.效果
显示全部的效果
模糊查询