PreparedStatement增删改查

为了防止SQL注入，使用PreparedStatement而不是Statement ；

Oracle

传入值的时候，不用单引号
例如select * from employee where name=?
如果不传值，值是写死的字符串类型，要加单引号
例如select * from employee where name='zhangsan'
如果要模糊查询，语句中不写%，也不加单引号，变量中写%，
例如SELECT * FROM employee where name like ?

name="%zhang%";
pre.setString(1,name);

 // 载入驱动
  Class.forName("oracle.jdbc.driver.OracleDriver");
  java.sql.DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  
  //建立连接
  String url = "jdbc:oracle:thin:@192.168.0.0:1521:ORCL";
  String user="admin";
  String password="pwd";
  Connection con=java.sql.DriverManager.getConnection(url, user, password);	

无条件查所有

String sql="select * from employee";
PreparedStatement pre=con.prepareStatement(sql);
ResultSet rs=pre.executeQuery();
while (rs.next()) {
	String id = rs.getString("id");
	System.out.println("id:" + id);
}

有条件查询

name="zhangsan";
String sql="select * from employee where name=?";
//使用In:String strSql = "select * from Salary where name in(?) order by ID";
PreparedStatement pre=con.prepareStatement(sql);
pre.setString(1,name);
ResultSet rs=pre.executeQuery();
while (rs.next()) {
	String id = rs.getString("id");
	System.out.println("id:" + id);
}

模糊查询

name="%zhang%";
String sql = "SELECT * FROM employee where name like ?";
PreparedStatement pre=con.prepareStatement(sql);
pre.setString(1,name);
ResultSet rs=pre.executeQuery();
while (rs.next()) {
	String id = rs.getString("id");
	System.out.println("id:" + id);
}

根据条件删除

name="zhangsan";
String sql = "delete FROM employee where name =?";
PreparedStatement pre=con.prepareStatement(sql);
pre.setString(1,name);
pre.executeUpdate();

插入数据

name="zhangsan";
String sql="insert into employee(id,name) values(1,?)"
PreparedStatement pre=con.prepareStatement(sql);
pre.setString(1,name);
pre.executeUpdate();

//直接写的字符串类型要加单引号,int类型不用单引号
String sql="insert into employee(id,name) values(1,'zhangsan')"
PreparedStatement pre=con.prepareStatement(sql);
pre.executeUpdate();