//代码复制出来,修改一下bean,加载一下jar包,能直接用的,不懂加我Q 983331283
package cn.tootoo.entity;
public class Page {
private int pageSize;//每页显示几条
private int pageNumber;//第几页
private int dataCount;//数据总个数
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getPageNumber() {
return pageNumber;
}
public void setPageNumber(int pageNumber) {
this.pageNumber = pageNumber;
}
public int getDataCount() {
return dataCount;
}
public void setDataCount(int dataCount) {
this.dataCount = dataCount;
}
}
package cn.tootoo.db.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import cn.tootoo.entity.Chemical;
import cn.tootoo.entity.Page;
import cn.tootoo.entity.Seed;
import cn.tootoo.entity.Users;
public class MysqlBaseDao {
public final static String DRIVER = "com.mysql.jdbc.Driver"; //mysql驱动
//ip地址 端口 数据库名称
public final static String URL = "jdbc:mysql://127.0.0.1:3306/php?characterEncoding=UTF-8";//url连接
public final static String DBNAME = "root"; // 用户名
public final static String DBPASS = "root"; // 密码
private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
/**
* 获得链接
* @throws ClassNotFoundException
* @throws SQLException
* @return 返回连接
*/
public Connection getConn() throws ClassNotFoundException, SQLException{
Class.forName(DRIVER); //加载驱动类
Connection conn = DriverManager.getConnection(URL,DBNAME,DBPASS); //获取连接
return conn; //返回连接
}
/**
* 关闭所有的连接
* @param conn
* @param pstmt PreparedStatement
* @param rs
*/
public void closeAll( Connection conn, PreparedStatement pstmt, ResultSet rs ) {
if(rs != null){
try
{
rs.close();
} catch (SQLException e)
{
e.printStackTrace();
}
}
if(pstmt != null){
try
{
pstmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if(conn != null){
try
{
conn.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
/**
*执行sql的语句,删除增加修改 ִ
* @param sql 入参
* @param param 查询条件的参数数组
* @return 返回执行结果
*/
public int executeSQL(String preparedSql,String[] param) {
Connection conn = null;
PreparedStatement pstmt = null;
int num = 0;
try {
conn = getConn();
pstmt = conn.prepareStatement(preparedSql);
if( param != null ) {
for( int i = 0; i < param.length; i++ ) {
pstmt.setString(i+1, param[i]);
}
}
num = pstmt.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(conn,pstmt,null);
}
return num;
}
/**
* 查询化肥总条数
*
* @param userName
* @param password
*/
public int findSeedCount( ) {
String sql = "select count(*) as count from news ";
int i=0;
try {
conn = this.getConn();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if (rs.next()) {
i = rs.getInt("count");
return i;
}else{
return 0;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
this.closeAll(conn, pstmt, rs);
}
return i;
}
/**
* 查询化肥信息
* @return
*/
public List<Chemical> getChemicalList(int landId){
List<Chemical> seedList =new ArrayList<Chemical>();
String sql = "select * from chemical where landId ="+landId+" order by id desc ";
System.out.println("###查询分页sql语句是:"+sql);
Chemical seedReturn = null;
try {
conn = this.getConn();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
seedReturn =new Chemical();
seedReturn.setId(rs.getInt("id"));
seedReturn.setBrand(null == rs.getString("brand")?"":rs.getString("brand"));
seedReturn.setManufactor(null == rs.getString("manufactor")?"":rs.getString("manufactor"));
seedReturn.setPrice(rs.getDouble("price"));
seedReturn.setBuyTime(null == rs.getString("buyTime")?"":rs.getString("buyTime"));
seedReturn.setLongitude(null == rs.getString("longitude")?"":rs.getString("longitude"));
seedReturn.setLatitude(null == rs.getString("latitude")?"":rs.getString("latitude"));
seedReturn.setTime(null == rs.getString("writetime")?"":rs.getString("writetime"));
seedReturn.setLandId(rs.getInt("landId"));
seedList.add(seedReturn);
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("###查询化肥信息报错");
} finally {
this.closeAll(conn, pstmt, rs);
}
System.out.println("###查询化肥信息完成 返回数据 cheemicalList:"+seedList.size());
return seedList;
}
/**
* mysql分页查询化肥信息
* @return
*/
public List<Users> getSeedList(Users User,Page page){
//数据总条数
page.setDataCount(this.findSeedCount());
if(0 >= page.getDataCount()){
return null;
}
// 总页数
int pageCount = 0;
if(0 >= page.getPageSize()){
page.setPageSize(10);
}
if(0 >= page.getPageNumber()){
page.setPageNumber(1);
}
int mm = page.getDataCount()/page.getPageSize();
int nn = page.getDataCount()%page.getPageSize();
if(nn > 0 && mm <= 0){
pageCount = 1;
}else if(mm > 0 && nn > 0){
pageCount = mm + 1;
}else if(mm > 0 && nn <= 0){
pageCount = mm ;
}
if(page.getPageNumber() >= pageCount){
page.setPageNumber(pageCount);
}else if(page.getPageNumber() <= 0){
page.setPageNumber(1);
}
List<Users> seedList =new ArrayList<Users>();
String sql2 = " select * from news limit "+((page.getPageNumber() -1)*page.getPageSize())+","+page.getPageSize();
System.out.println("###查询分页sql语句是:"+sql2);
Users seedReturn = null;
try {
conn = this.getConn();
pstmt = conn.prepareStatement(sql2);
rs = pstmt.executeQuery();
while (rs.next()) {
seedReturn =new Users();
seedReturn.setId(rs.getString("newsID"));
seedReturn.setName(null == rs.getString("title")?"":rs.getString("title"));
seedList.add(seedReturn);
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("###查询化肥信息报错");
} finally {
this.closeAll(conn, pstmt, rs);
}
return seedList;
}
/**
* 通过id查询化肥信息
* @param id
*/
public Seed findSeed(Seed seed){
String sql = "select * from seed where id=? ";
Seed seedReturn = null;
try {
seedReturn =new Seed();
conn = this.getConn();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, seed.getId());
rs = pstmt.executeQuery();
if (rs.next()) {
seedReturn.setId(rs.getInt("id"));
seedReturn.setSeedName(null == rs.getString("seedName")?"":rs.getString("seedName"));
seedReturn.setCategory(null == rs.getString("category")?"":rs.getString("category"));
seedReturn.setManufactor(null == rs.getString("manufactor")?"":rs.getString("manufactor"));
seedReturn.setPrice(rs.getDouble("price"));
if(null != rs.getString("buyTime")){
seedReturn.setBuyTime(rs.getString("buyTime"));
}
seedReturn.setBuyPlace(null == rs.getString("buyPlace")?"":rs.getString("buyPlace"));
seedReturn.setLongitude(null == rs.getString("longitude")?"":rs.getString("longitude"));
seedReturn.setLatitude(null == rs.getString("latitude")?"":rs.getString("latitude"));
if(null != rs.getString("writetime")){
seedReturn.setTime(rs.getString("writetime"));
}
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("###查询化肥信息报错");
} finally {
this.closeAll(conn, pstmt, rs);
}
return seedReturn;
}
/**
* 化肥信息新增
* @param userName
* @param password
* @return int
*/
public int insertChemical(Chemical seed) {
int count=0;
System.out.println("###进入到化肥新增dao层");
try {
System.out.println("dao层内部176");
conn=this.getConn();
String sql = "insert into chemical(brand,manufactor,price,buyTime"
+ ",longitude,latitude,writetime,landId) values(?,?,?,?,?,?,?,?) ";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, seed.getBrand());
pstmt.setString(2, seed.getManufactor());
pstmt.setDouble(3, seed.getPrice());
pstmt.setString(4, seed.getBuyTime());
pstmt.setString(5, seed.getLongitude());
pstmt.setString(6, seed.getLatitude());
pstmt.setString(7, seed.getTime());
pstmt.setInt(8, seed.getLandId());
count=pstmt.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
this.closeAll(conn, pstmt, rs);
System.out.println("###化肥dao层 新增完毕 是否成功:count:"+count);
return count;
}
/**
* 查询所有用户
* @return
*/
public List selectAllUser(){
List list=new ArrayList();
String sql = "select * from fm_user ";
try {
conn = this.getConn();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
Users user = new Users();
user.setId(rs.getString("userID"));
user.setName(rs.getString("userName"));
list.add(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
this.closeAll(conn, pstmt, rs);
}
return list;
}
/**
* 删除
* @param userID
* @return ִ
*/
public int deleteUserByID(String userID){
String sql="delete from fm_user where userID = ? ";
String[] param = new String[]{ userID };
return this.executeSQL(sql, param);
}
/**
*
* @return
*/
public List findFace(){
List list=new ArrayList();
String sql="select * from user";
Users user=null;
try {
conn = this.getConn();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
user =new Users();
user.setId(rs.getString("userID"));
user.setName(rs.getString("userName"));
list.add(user);
}
} catch (Exception e) {
}finally{
this.closeAll(conn, pstmt, rs);
}
return list;
}
/**
* 根据用户名查询用户信息
* @param userName
* @return
*/
public Object findUser(String userName){
String sql = "select * from users where userName=? ";
try {
conn = this.getConn();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userName);
rs = pstmt.executeQuery();
if (rs.next()) {
Users user=new Users();
user.setId(rs.getString("userID"));
user.setName(rs.getString("userName"));
return user;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
this.closeAll(conn, pstmt, rs);
}
return null;
}
public static void main(String[] args) {
UUID uuid = UUID.randomUUID();
String uid=uuid.toString().replaceAll("-","");
System.out.println(uid.substring(0,5));
int i = 5;
int m=10;
System.out.println("mm:"+i/m);
MysqlBaseDao seedDao=new MysqlBaseDao();
Page page=new Page();
page.setPageNumber(3);
page.setPageSize(5);
Users user=new Users();
List<Users> list= seedDao.getSeedList(user, page);
for(Users sed:list){
System.out.println("id:"+sed.getId());
}
System.out.println("总共:"+list.size());
}
}
相关推荐
springboot项目,基于SpringBoot+Vue实现增删改查分页DEMO(源码+数据库) 技术栈 数据库:MySQL 后端框架:SpringBoot+Spring Data JPA 前端框架:Vue-ElementUI
springboot+JPA简单的示例,代码有注释。带有mysql的sql语句,编写环境是java8, ide是IDEA,连接数据库的连接需要修改,需要安装lombok插件,以免写set和get方法。代码中有分页查询等示例。
mysql语法大全总结,内含DDL,DML,DQL,DCL等语句,可作为工作学习的小字典,随时查看学习,查询所有数据库、查询当前数据库、创建数据库、删除数据库、切换数据库、查询当前数据库的所有表、查看指定表结构、查询指定...
2.优化多条件查询,支持or语句,支持多种排序,支持自定义返回字段,支持自增语句 3.支持一次插入多条 4.支持外部connention 5.优化分页查询 6.支持手动开启或关闭日志打印 7.优化对特殊符号的支持 博文:...
通过SQL 查询分析器,显示比较:我的结论是: 分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句 分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句 分页方案三:(利用...
用ssh框架写的一个小型购物车,包含登陆,分页,增删改查,购物车,订单各项功能,有数据库mysql语句,各个jar包也有,只是页面比较交单,但功能齐全,适合初学者!
利用select语句的一个特性就可以很方便地实现mysql查询结果的分页,下文对该方法的实现过程作了详细的介绍,希望对您能有所启迪。 mysql分页查询是我们经常见到的问题,那么应该如何实现呢?下面就教您一个实现mysql...
主要介绍了java连接oracle、sqlserver和mysql语句,以及连接这些数据库的分页语句
几条常见的数据库分页SQL 语句,针对oracle,sqlserver,mysql三种常见数据库的分页显示。
MySQL数据库实现的分页实例 先导入SQL语句 Web
最近简单的对oracle,mysql,sqlserver2005的数据分页查询作了研究,把各自的查询的语句贴出来供大家学习….. (一)、mysql的分页查询 mysql的分页查询是最简单的,借助关键字limit即可实现查询,查询语句通式: ...
主要介绍了mysql分页原理和高效率的mysql分页查询语句,大家参考使用吧
Mysql的分页的两个参数 select * from user limit 1,2 ... 您可能感兴趣的文章:详解MySQL的limit用法和分页查询语句的性能分析MYSQL分页limit速度太慢的优化方法mysql limit分页优化方法分享Mysql limit
一个封装好的php+mysql分页类,分页显示MySQL数据库中的数据,根据SQL查询语句从表中读取相应的记录,显示首页、下页、上页、未页。
主要介绍了Oracle、MySQL和SqlServe三种数据库分页查询语句的区别介绍 的相关资料,需要的朋友可以参考下
在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能。 SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句...
php分页的MySQL语句,里面有详细的分页代码,很适合现在的程序开发
但问题就出在这种通用分页存储过程是在存储过程内部进行SQL语句拼接,根本无法修改为参数化的查询语句,因此这种通用分页存储过程是不可取的。但是如果不用通用的分页存储过程,则意味着必须为每个具体的分页查询写...
您可能感兴趣的文章:从一个MySQL的例子来学习查询语句mysql分页原理和高效率的mysql分页查询语句mysql查询语句通过limit来限制查询的行数mysql优化limit查询语句的5个方法优化MySQL数据库中的查询语句详解整理MySql...
数据库SQL优化是老生常谈的问题,在面对百万级数据量的分页查询,又有什么好的优化建议呢?下面将列举了一些常用的方法,供大家参考学习! 方法1: 直接使用数据库提供的SQL语句 语句样式: MySQL中,可用如下方法: ...