引言
最近想在網上找些使用jdbc連接操作SQLserver的完成模糊查詢或者多條件Java代碼,可是網上寫的魚龍混雜,很少有關於Java使用jdbc操作SQLServer的模糊查詢和多條件查詢的資料,不過還好最終我解決了,考慮到一部分人還是在使用SQLServer,所以把這查詢的方法分享給大家。
準備工作
開發工具:eclipse
資料庫:SQLserver2014,其他版本也可以
記住導入sqljdbc4.jar包
創建資料庫db_employe,表sal
創建Sal實體類
public class Sal{
private Integer id;
private String empno;
private String name;
private String createDate;//發工資日期
private double sal;
private double salDecrease;//應扣費用
setter和getter方法//省略不寫
public String toString() {//toString方法
return "Sal [id=" + id + ", empno=" + empno + ",
name=" + name + ", createDate=" + createDate + ", sal=" + sal
+ ", salDecrease=" + salDecrease + "]";
}
}
創建資料庫連接
public class DbConn{
private String Driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private String URL = "jdbc:sqlserver://localhost:1433;DatabaseName=db_employee_sal";
private String Username="sa",Password="123456";
protected Connection conn = null;
public DbConn() {
Connection conn = 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 List<Sal> selectAll(){ List<Sal> list =new ArrayList<Sal>(); 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(); } } return list; }}
調用後
單條件模糊查詢
單條件模糊查詢,就是根據一個欄位查詢,模糊匹配%變量%
public class SalDaoImpl extends DbConn{public List<Sal> findByMap(String empno) { List<Sal> list =new ArrayList<Sal>(); 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<Sal> list=new SaoImpl().findByMap("10005");
}
}
多條件模糊查詢
根據多個參數來查詢,也就是多條件查詢
public class SalDaoImpl extends DbConn{public List<Sal> findByMap(String empno,String createDate) { List<Sal> list =new ArrayList<Sal>(); 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中就是這個樣子