//用户登录查询:
public boolean login(String name, String password) {
Connection con=null;
PreparedStatement pst=null;
ResultSet rs=null;
try {
con=source.getConnection();
System.out.println("得到连接"+con);
String sql="select * from teacher where tea_name=? and tea_password=?";
//执行sql语句
pst=con.prepareStatement(sql);
//设定问号的值
pst.setString(1, name);
pst.setString(2, password);
rs=pst.executeQuery();
//ResultSet是永远不会null
if(rs.next()){
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
con.close();
pst.close();
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
//根据ID删除信息
public boolean t_delSubject(String id) {
Connection con=null;
PreparedStatement pst=null;
try {
con=source.getConnection();
System.out.println("删除操作得到连接"+con);
String sql="delete from selectedresult where id=?";
//执行sql语句
pst=con.prepareStatement(sql);
//设定问号的值
pst.setInt(1, new Integer(id).intValue());
//除执行查询外,其他操作都会返回flase
/*boolean flag=pst.execute();
if(flag==true)
{
return true;
}*/
int result=pst.executeUpdate();
if(result!=0){
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
con.close();
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
//根据ID查询信息
public List t_querySubject(String id) {
Connection con=null;
PreparedStatement pst=null;
ResultSet rs=null;
try {
con=source.getConnection();
System.out.println("根据ID查询选题得到连接"+con);
String sql="select * from selectedresult where id=?";
//执行sql语句
pst=con.prepareStatement(sql);
//设定问号的值
pst.setInt(1, new Integer(id).intValue());
rs=pst.executeQuery();
if(rs.next()){
/*
* 在这一部分要做的是返回List集合
* 要建立一个JavaBean来存储得到的信息(省略)
* */
int id=rs.getInt("id");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
con.close();
pst.close();
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
设置选题(批量插入数据)
public boolean t_setSubject(List list) {
Connection con=null;
PreparedStatement pst=null;
ResultSet rs=null;
String sql="insert into selectedresult" +
"(ktmc,syzy,zdjs,tea_id,ktlx,zdrs,ktyq,add_time,s_number) values" +
"(?,?,?,?,?,?,?,?,?,?)";
//接收到客户端的信息后把信息组成一个list集合然后把信息取出来
for(int i=0;i<list.size();i++)
{
//得到javaBean
SubjectBean subjectBean=(SubjectBean)list.get(i);
//把javaBean的信息取出来,然后马上存到数据库
}
return false;
}
批量插入数据例子:
PreparedStatement ps =
conn.prepareStatement( "INSERT into employees values (?, ?, ?)");
for (n = 0; n < 100; n++) {
ps.setString(name[n]);
ps.setLong(id[n]);
ps.setInt(salary[n]);
ps.addBatch();
}
ps.executeBatch();
一个批量插入应用的例子
在java中对数据库进行更新操作时,可以利用addBatch()方法,将多条update、delete等更新语句绑在一起,最后执行一次即可,这个有点像TOAD中按脚本执行的那个按钮的功能,很是好用,避免写一条语句就更新执行一次,还影响效率。
代码框架如下:
String url = "jdbc:oracle:thin:@127.0.0.1:1521:oracle";
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(url,"username","password");
Statement smt = conn.createStatement();
String sql1= "update table1 set col1 = 'newdata1'";
一个事务处理应用的例子
connection.setAutoCommit(false);
PreparedStatement statement =
connection.prepareStatement("INSERT INTO TABLEX VALUES(?, ?)");
statement.setInt(1, 1);
statement.setString(2, "Cujo");
statement.addBatch();
statement.setInt(1, 2);
statement.setString(2, "Fred");
statement.addBatch();
statement.setInt(1, 3);
statement.setString(2, "Mark");
statement.addBatch();
int [] counts = statement.executeBatch();
connection.commit();
String sql2= "update table2 set col2 = 'newdata2'";
String sql3= "update table3 set col3 = 'newdata3'";
smt.addBatch(sql1);
smt.addBatch(sql2);
smt.addBatch(sql3);
smt.executeBatch();
if(smt!=null) smt.close();
if(conn!=null) conn.close();