java连接JDBC用工具类对数据库进行动态查询(简单)
public class Demo {
public static void main(String[] args) {
queryXsh("乔","女");
}
//模糊查询姓名
public static void queryXsh(String name,String sex) {
PreparedStatement ps = null;
Connection con = null;
ResultSet rs = null;
try {
//调用了工具类中的getConnection方法
con = JDBCUtils.getConnection();
//where后面的条件恒成立,为了可以追加条件
String sql="select * from emp where 1=1";
if(name!=null){
//注意and前面要有空格
sql+=" and ename like ?";
}
if(sex !=null){
sql+=" and esex = ?";
}
ps=con.prepareStatement(sql);
//使用外部计数器来计数条件的数量
int count=1;
if (name!=null){
ps.setString(count++,"%" + name + "%");
}
if (sex!=null){
ps.setString(count++,sex);
}
rs = ps.executeQuery();
while (rs.next()) {
int eno = rs.getInt("eno");
String ename = rs.getString("ename");
String esex = rs.getString("esex");
double salary = rs.getDouble("salary");
String pwd = rs.getString("pwd");
int dno = rs.getInt("dno");
System.out.println(eno+"--"+ename+"--"+esex+"--"+salary+"--"+pwd+"--"+dno);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//调用工具类中的closeALL方法,释放资源
JDBCUtils.closeAll(rs,ps,con);
}
}
}
工具类
//工具类
public class JDBCUtils {
private static final Properties pro=new Properties();
static {
try {
pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
Class.forName(pro.getProperty("driver"));
} catch (ClassNotFoundException | IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection con=null;
try {
con= DriverManager.getConnection(pro.getProperty("url"),pro.getProperty("username"),pro.getProperty("password"));
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
public static void closeAll(ResultSet resultSet, Statement statement,Connection connection){
try {
if (resultSet!=null){
resultSet.close();
}
if (statement!=null){
statement.close();
}
if (connection!=null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
properties配置文件
//jdbc.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/xsh
username=root
password=123