java的sqlserver连库信息 包括增删改查 分页查询 java的 SqlServer 连库信息大全包括增删改查分页查询增删改查
//代码复制出来，修改一下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 cn.tootoo.entity.Chemical;
import cn.tootoo.entity.Page;
import cn.tootoo.entity.Seed;
import cn.tootoo.entity.Users;
public class SqlServerBaseDao {
public final static String DRIVER = "com.mysql.jdbc.Driver"; //mysql驱动
public final static String URL = "jdbc:mysql://localhost: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{
return this.getconnSQLserver();
}
public static Connection getconnSQLserver() {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
.newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String urlsa = "jdbc:sqlserver://127.0.0.1:1433;databaseName=farm;user=sa;password=12345%$#@!12&*";// sa身份连接
String url2 = "jdbc:sqlserver://127.0.0.1:1433;databaseName=farm;integratedSecurity=true;";// windows集成模式连接
Connection conn = null;
try {
conn = DriverManager.getConnection(urlsa);
System.out.println("###db.connection:success");
} catch (SQLException e) {
e.printStackTrace();
}
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(Seed seed) {
Users user = null;
String sql = "select count(*) as count from seed ";
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;
}
/**
* 分页查询化肥信息
* @return
*/
public List<Seed> getSeedList(Seed seed,Page page){
//数据总条数
page.setDataCount(this.findSeedCount(seed));
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<Seed> seedList =new ArrayList<Seed>();
String sql = "select * from ( select ROW_NUMBER() over(order by id) as num,"
+ "* from seed) as t where t.num between "+((page.getPageNumber() -1)*page.getPageSize()+1)+" and "+(page.getPageNumber()*page.getPageSize());
System.out.println("###查询分页sql语句是："+sql);
Seed seedReturn = null;
try {
conn = this.getConn();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
seedReturn =new Seed();
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"));
}
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;
}
public Users 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;
}
}