使用statement进行数据的查询,基本步骤如下:
* 1. 初始化simpleDbSource对象
* 2. 获得getconnection
* 3. createStatement 获得查询语句
* 4. executeUpdate, 执行更新语句
* 5. 关闭使用的statement, connection, 注意次序不要弄错
*
* 注意:更新语句,执行过一次后,column需要递增,否则报错
/**
*
*/
package db;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @author sean
*
* 1. 初始化simpleDbSource对象
* 2. 获得getconnection
* 3. createPreparedStatement 获得查询语句
* 4. 设置具体更新内容,setInt(colIndex, value), setString(colIndex,value)
* 4. executeUpdate, 执行更新语句
* 5. 关闭使用的PreparedStatementstatement, connection, 注意次序不要弄错
*
* 注意:更新语句,执行过一次后,column需要递增,否则报错
*/
public class PreparedStatementDemo {
private static String querySql ="select * from user";
private static String pstmtSql = "insert into user values(?,?,?,?)";
Connection conn1;
static Statement stmt;
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
DBSource dbSource;
Connection conn = null;
java.sql.PreparedStatement pstmt = null;
try {
dbSource = new SimpleDBSource();
conn = dbSource.getConnect();
pstmt = conn.prepareStatement(pstmtSql);
pstmt.setInt(1, 9);
pstmt.setString(2, "sean");
pstmt.setString(3, "my@hotmail.com");
pstmt.setString(4, "add some comments");
//数据库更新工作,包括create, drop, update, insert etc.
pstmt.executeUpdate();
//清空设置的参数,为后续更新准备
pstmt.clearParameters();
System.out.println("执行成功"+ pstmtSql);
//进行数据库查询
Connection conn1 = dbSource.getConnect();
Statement stmt = conn1.createStatement();
ResultSet rs = stmt.executeQuery(querySql);
//进行遍历
while(rs.next()){
System.out.println(rs.getInt(1)+ "\t");
System.out.println(rs.getString(2)+ "\t");
System.out.println(rs.getString(3)+ "\t");
System.out.println(rs.getString(4)+ "\t");
System.out.println("**********************");
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//依次关闭statement和conn数据库连接对象,清空资源
finally{
if(stmt!= null){
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
stmt= null;
}
if(pstmt!= null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
pstmt= null;
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
conn= null;
}
}
}
}
* 1. 初始化simpleDbSource对象
* 2. 获得getconnection
* 3. createStatement 获得查询语句
* 4. executeUpdate, 执行更新语句
* 5. 关闭使用的statement, connection, 注意次序不要弄错
*
* 注意:更新语句,执行过一次后,column需要递增,否则报错
/**
*
*/
package db;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author sean
*
* 1. 初始化simpleDbSource对象
* 2. 获得getconnection
* 3. createStatement 获得查询语句
* 4. executeUpdate, 执行更新语句
* 5. 关闭使用的statement, connection, 注意次序不要弄错
*
* 注意:更新语句,执行过一次后,column需要递增,否则报错
*/
public class StatementDemo {
private static String insertSql="insert into user values('7','sean','sean@mail.com','hellofromsean')";
private static String querySql ="select * from user";
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
DBSource dbSource;
Connection conn = null;
java.sql.Statement stmt = null;
try {
dbSource = new SimpleDBSource();
conn = dbSource.getConnect();
stmt = conn.createStatement();
//数据库更新工作,包括create, drop, update, insert etc.
stmt.executeUpdate(insertSql);
System.out.println("执行成功"+ insertSql);
//进行数据库查询
ResultSet rs = stmt.executeQuery(querySql);
//进行遍历
while(rs.next()){
System.out.println(rs.getInt(1)+ "\t");
System.out.println(rs.getString(2)+ "\t");
System.out.println(rs.getString(3)+ "\t");
System.out.println(rs.getString(4)+ "\t");
System.out.println("**********************");
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//依次关闭statement和conn数据库连接对象,清空资源
finally{
if(stmt!= null){
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
stmt= null;
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
conn= null;
}
}
}
}
/**
*
*/
package db;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @author sean
*
* 1. 初始化simpleDbSource对象
* 2. 获得getconnection
* 3. createPreparedStatement 获得查询语句
* 4. 设置具体更新内容,setInt(colIndex, value), setString(colIndex,value)
* 4. executeUpdate, 执行更新语句
* 5. 关闭使用的PreparedStatementstatement, connection, 注意次序不要弄错
*
* 注意:更新语句,执行过一次后,column需要递增,否则报错
*/
public class PreparedStatementDemo {
private static String querySql ="select * from user";
private static String pstmtSql = "insert into user values(?,?,?,?)";
Connection conn1;
static Statement stmt;
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
DBSource dbSource;
Connection conn = null;
java.sql.PreparedStatement pstmt = null;
try {
dbSource = new SimpleDBSource();
conn = dbSource.getConnect();
pstmt = conn.prepareStatement(pstmtSql);
pstmt.setInt(1, 9);
pstmt.setString(2, "sean");
pstmt.setString(3, "my@hotmail.com");
pstmt.setString(4, "add some comments");
//数据库更新工作,包括create, drop, update, insert etc.
pstmt.executeUpdate();
//清空设置的参数,为后续更新准备
pstmt.clearParameters();
System.out.println("执行成功"+ pstmtSql);
//进行数据库查询
Connection conn1 = dbSource.getConnect();
Statement stmt = conn1.createStatement();
ResultSet rs = stmt.executeQuery(querySql);
//进行遍历
while(rs.next()){
System.out.println(rs.getInt(1)+ "\t");
System.out.println(rs.getString(2)+ "\t");
System.out.println(rs.getString(3)+ "\t");
System.out.println(rs.getString(4)+ "\t");
System.out.println("**********************");
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//依次关闭statement和conn数据库连接对象,清空资源
finally{
if(stmt!= null){
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
stmt= null;
}
if(pstmt!= null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
pstmt= null;
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
conn= null;
}
}
}
}