引言
最近想在网上找些使用jdbc连接操作SQLserver的完成模糊查询或者多条件Java代码,可是网上写的鱼龙混杂,很少有关于Java使用jdbc操作SQLServer的模糊查询和多条件查询的资料,不过还好最终我解决了,考虑到一部分人还是在使用SQLServer,所以把这查询的方法分享给大家。
准备工作
开发工具:eclipse 数据库:SQLserver2014,其他版本也可以 记住导入sqljdbc4.jar包
创建数据库db_employe,表sal
创建Sal实体类
public class Sal{
private Integerid;
private Stringempno;
private Stringname;
private StringcreateDate;//发工资日期
private doublesal;
private doublesalDecrease;//应扣费用
setter和getter方法//省略不写
public String toString() {//toString方法
return "Sal [id=" + id + ", empno=" + empno + ",
name=" + name + ", createDate=" + createDate + ", sal=" + sal
+ ", salDecrease=" + salDecrease + "]";
}
}
创建数据库连接
public class DbConn{
private StringDriver= "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private StringURL= "jdbc:sqlserver://localhost:1433;DatabaseName=db_employee_sal";
private String Username="sa",Password="123456";
protected Connectionconn= null;
public DbConn() {
Connectionconn= null;
try {
Class.forName(Driver);
conn= DriverManager.getConnection(URL,Username,Password);
}
catch(java.lang.ClassNotFoundException e) {
System.err.println( e.getMessage());
}
catch(SQLException e) {
System.err.println( e.getMessage());
}
this.conn=conn;
}
public void close(){
try{
if (conn!=null)
conn.close();
}
catch (SQLException e) {
System.out.println(e.getMessage());
}
}
public static void main(String[] args) {
System.out.println(new DbConn());//测试连接
}
}
查询所有
查询所有,Sqlserver的sql语句为select * from [dbo].[sal],Statement接口仅支持查询。
public class SalDaoImpl extends DbConn{public ListselectAll(){Listlist=new ArrayList();if(conn!=null){try{String sql="select * from [dbo].[sal]";//SQLserver的写法String sql="select * from sal";//Mysql的写法Statement stmt=conn.createStatement();ResultSet rs=stmt.executeQuery(sql);while(rs.next()){Sal r=new Sal();r.setId(rs.getInt("id"));r.setEmpno(rs.getString("empno"));r.setName(rs.getString("name"));r.setCreateDate(rs.getString("createDate"));r.setSal(rs.getDouble("sal"));r.setSalDecrease(rs.getDouble("salDecrease"));list.add(r);}rs.close();}catch(Exception e){e.printStackTrace();}}returnlist;}}
调用后
单条件模糊查询
单条件模糊查询,就是根据一个字段查询,模糊匹配%变量%
public class SalDaoImpl extends DbConn{public List findByMap(String empno) { List list =new ArrayList(); if(conn!=null) { try{ String sql="select * from [dbo].[sal] where empno like '%"+empno+"'"; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while(rs.next()){ Sal r =new Sal(); r.setId(rs.getInt("id")); r.setEmpno(rs.getString("empno")); r.setName(rs.getString("name")); r.setCreateDate(rs.getString("createDate")); r.setSal(rs.getDouble("sal")); r.setSalDecrease(rs.getDouble("salDecrease")); list.add(r); } rs.close(); }catch(Exception e){ e.printStackTrace(); } } return list; }
public static void main(String args[]){
List list=new SaoImpl().findByMap("10005");
}
}
多条件模糊查询
根据多个参数来查询,也就是多条件查询
public class SalDaoImpl extends DbConn{public List findByMap(String empno,String createDate) { List list =new ArrayList(); if(conn!=null) { try{ String sql="select * from [dbo].[sal] where empno like '%"+empno+"'and createDate like'%"+createDate+"'"; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while(rs.next()){ Sal r =new Sal(); r.setId(rs.getInt("id")); r.setEmpno(rs.getString("empno")); r.setName(rs.getString("name")); r.setCreateDate(rs.getString("createDate")); r.setSal(rs.getDouble("sal")); r.setSalDecrease(rs.getDouble("salDecrease")); list.add(r); } rs.close(); }catch(Exception e){ e.printStackTrace(); } } return list; } }
如果整合到Java web中就是这个样子