statement 操作数据库创建接口对象
create sequence 序列
executeUpdate(sql);//增删改操作
eg
1.创建一个数据库member字段为mid,name,age,birthday,note
2.
public class Test1 {
//插入
public static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
public static final String DB_USER = "scott";
public static final String DB_PASS = "a123456";
public static void main(String[] args) throws Exception {
Connection conn = null;
Statement stmt = null;
// TODO Auto-generated method stub
Class.forName(DB_DRIVER);//加载驱动
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
stmt = conn.createStatement();
String sql = " insert into member(mid,name,age,birthday,note)"+
"values(myseq.nextval,'zhangsan',20,sysdate,'一个新员工')";
int len = stmt.executeUpdate(sql);//增删改操作
System.out.println("更新行数"+len);
stmt.close();
conn.close();
}
}
使用Statement接口进行查询
使用ResultSet装下整个查询结果
取出数据时判断是否有数据 next
取得指定类型数据 getXxx(列的标记)
关闭 close
使用ResultSet取出数据
eg
public class Test2 {
//查询
public static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
public static final String DB_USER = "scott";
public static final String DB_PASS = "a123456";
public static void main(String[] args) throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
// TODO Auto-generated method stub
Class.forName(DB_DRIVER);//加载驱动
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
stmt = conn.createStatement();
String sql = " select mid,name,age,birthday,note from member";
rs = stmt.executeQuery(sql);
while(rs.next()) {
int mid = rs.getInt("mid");
String name = rs.getString("name");
int age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
String note = rs.getString("note");
System.out.println(mid+" "+name+" "+age+" "+birthday+" "+note+" ");
}
rs.close();
stmt.close();
conn.close();
}
}
预处理PreparedStatement
先在数据库之中执行要操作的SQL语句,但是对其对应的内容占时不插入,通过程序再依次设置
简化 可以防止sql注入
(指web应用程序对用户输入数据的合法性没有判断或过滤不严,
可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句)
eg
1.预处理插入
public class Test3 {
//预处理插入
public static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
public static final String DB_USER = "scott";
public static final String DB_PASS = "a123456";
public static void main(String[] args) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
// TODO Auto-generated method stub
Class.forName(DB_DRIVER);//加载驱动
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
String name = "lisi";
int age = 20;
Date birthday = new Date();
String note = "新员工lisi";
String sql = " insert into member(mid,name,age,birthday,note)"+
"values(myseq.nextval,?,?,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);//1表示问号的索引
pstmt.setInt(2, age);
pstmt.setDate(3, new java.sql.Date(birthday.getTime()));
pstmt.setString(4, note);
int len = pstmt.executeUpdate();//增删改操作
System.out.println("更新"+len);
pstmt.close();
conn.close();
}
}
2.预处理查询
public class Test4 {
//预处理查询
public static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
public static final String DB_USER = "scott";
public static final String DB_PASS = "a123456";
public static void main(String[] args) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// TODO Auto-generated method stub
Class.forName(DB_DRIVER);//加载驱动
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
String sql = "select mid,name,age,birthday,note from member where mid = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 5);
rs = pstmt.executeQuery();
while(rs.next()) {
int mid = rs.getInt("mid");
String name = rs.getString("name");
int age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
String note = rs.getString("note");
System.out.println(mid+" "+name+" "+age+" "+birthday+" "+note+" ");
}
rs.close();
pstmt.close();
conn.close();
}
}
PerpareStatement 半成品 不能直接执行 执行到客户端才执行
模糊查询 like
eg
public class Test5 {
//模糊查询
public static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
public static final String DB_USER = "scott";
public static final String DB_PASS = "a123456";
public static void main(String[] args) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// TODO Auto-generated method stub
Class.forName(DB_DRIVER);//加载驱动
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
String sql = "select mid,name,age,birthday,note from member where name like ?";
String keyword = "li";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "%"+keyword+"%");
rs = pstmt.executeQuery();
while(rs.next()) {
int mid = rs.getInt("mid");
String name = rs.getString("name");
int age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
String note = rs.getString("note");
System.out.println(mid+" "+name+" "+age+" "+birthday+" "+note+" ");
}
rs.close();
pstmt.close();
conn.close();
}
}
统计查询
eg
public class Test6 {
//统计查询
public static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
public static final String DB_USER = "scott";
public static final String DB_PASS = "a123456";
public static void main(String[] args) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// TODO Auto-generated method stub
Class.forName(DB_DRIVER);//加载驱动
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
String sql = "select count(mid) from member";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()) {
int count = rs.getInt(1);
System.out.println(count);
}
rs.close();
pstmt.close();
conn.close();
}
}
事务批量处理
大数据批量执行效率更高
Statement接口方法
增加一个执行的SQL:addBatch 没有执行
一次执行多条SQL:executeBatch
PreparedStatement接口方法
增加执行的SQL:addBatch
eg
public class Test7 {
//批处理
public static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
public static final String DB_USER = "scott";
public static final String DB_PASS = "a123456";
public static void main(String[] args) throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
// TODO Auto-generated method stub
Class.forName(DB_DRIVER);//加载驱动
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
conn.setAutoCommit(false);//自动提交设置
stmt = conn.createStatement();
try {
stmt.addBatch("insert into member(mid,name) values(myseq.nextval,'tom')");
stmt.addBatch("insert into member(mid,name) values(myseq.nextval,'jack')");
stmt.addBatch("insert into member(mid,name) values(myseq.nextval,'wangwu')");
int data[] = stmt.executeBatch();
System.out.println(Arrays.toString(data));
conn.commit();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
conn.rollback();//回滚
}
stmt.close();
conn.close();
}
}