Jdbc连接查询数据库的一些心得

JDBC编程步骤
使用statement和prestatement对象
1.使用DriverManager类注册特定厂商的JDBC驱动程序
Class.forName("org.apache.derby.jdbc.ClientDriver");
2.建立与DBMS的会话连接
String url = "jdbc:derby://localhost:1527/DBName";
Connection con = DriverManager.getConnection(url,"DBusername","DBpassword");
3.创建SQL查询对象
Statement stmt = con.createStatement();
4.提交查询并接受结果
String query = "select * from Person";
ResultSet rs = stmt.executeQuery(query);
插入/删除/更新均为stmt.executeUpdate(String sql);
方法区别：
executeQuery返回值为ResultSet对象
executeUpdate返回值为影响操作的行数
5.从包裹对象中提取数据
while(rs.next()){//判断是否还有下一行结果
System.out.println("name"+rs.getName());
System.out.println("sex"+rs.getSex());
//在同一行中 访问 即取得属性的顺序不变
}
Statement 和 Preparedstatement 对象的区别在于后者提供预处理机制,性能更好,更为灵活,更加安全
Statement实例:
public static void main(String[] args){
Class.forName("org.apache.derby.jdbc.ClientDriver");
String url = "jdbc:derby://localhost:1527/DBName";
Connection connection = DriverManager.getConnection(url,"root","root");
Statement stmt = connection.createStatement();//创建包裹对象
//查询数据
ResultSet rs = stmt.executeQuery("select * from test");
System.out.println("-%5d","-10s","-10s","id","name","password");
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String password = rs.getString("password");
System.out.println("-%5d","-10s","-10s","id","name","password");
}
rs.close();
//插入数据
rs = stmt.executeQuery("select max(id) from test");
int id=20;
String name="zyh";
String password="harbor";
String insertsql= "INSERT INTO TEST VALUES " + "("+id+","+name+",'"+password+"')";
int rows = stmt.executeUpdate(insertsql);
System.out.println(rows+"rows has affected");
//删除数据
String delsql="DELETE FROM TEST WHERE ID="+id;
rows = stmt.executeUpdate(delsql);
System.out.println(rows+"rows has affected");
stmt.close();
connection.close();
}
 
Prestatement实例:
public static void main(String[] args){
private final String url = "jdbc:derby://localhost:1527/DBName";
try{
Class.forName("org.apache.derby.jdbc.ClientDriver");
}catch(ClassNotFoundException e){e.getMessage();return;}
Connection connection = null;
PrepardStatement stmt = null;
ResultSet rs = null;
try{
connection = DriverManager.getConnection(url,"root","root");
//插入数据
stmt = connection.preparedStatement("selct max(id) from test");
rs = stmt.executeQuery();
rs.next();
int nextID = rs.getInt(1)+1;
rs.close();
stmt.close();
int id=nextID;
String name="harbor";
String password="blue";
stmt = connection.preparedStatement("INSERT INTO TEST VALUES(?,?,?)");
stmt.setInt(1,id);
stmt.setString(2,name);
stmt.setString(3,password);
int rows = stmt.executeUpdate();
System.out.println(rows+"rows has affected");
stmt.close();
 
//查询数据
stmt = connection.preparedStatement("selct * from test where id=?");
stmt.setId(1,id);
rs = stmt.executeQuery();
while(rs.next()){
id = rs.getInt("id");
name = rs.getString("name");
password = rs.getString("password");
System.out.println("-%5d","-10s","-10s","id","name","password");
}
rs.close();
stmt.close();
 
//删除数据
stmt = connection.preparedStatement("DELETE FROM TEST WHERE ID=?");
stmt.setId(1,id);
rows = stmt.executeUpdate();
System.out.println(rows+"rows has affected");
}catch(SQLException e){
e.printStackTrace();
}finally{
if(rs!=null){try{rs.close();}catch(SQLException e){}}
if(stmt!=null){try{stmt.close();}catch(SQLException e){}}
if(connection!=null){try{connection.close();}catch(SQLException e){}}
}
  
 
}
使用datasource对象(数据库连接池)
InitialContext ctx = new InitialContext();//在命名空间中用容器缺省的信息创建JDNI上下文
ds = (DataSource)ctx.lookup(jdbc/DBName);//用JDNI的名称获得DataSource实例
用法：
//服务组件中获取ds对象;
private DataSource ds=null;
try{
InitialContext ctx = new InitialContext();
ds = (DataSource)ctx.lookup("java:comp/env/jdbc/JDBCNAME");
}catch(Exception e){
throw new SQLException(e);
}
//可以写在静态语句块中,也可写在构造器当中
构造器：
public BookService() throws BookStorageException {
try {
Context ctx = new InitialContext();
DataSource dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/test");
bookDAO = new BookDAO(dataSource);
} catch (Exception e) {
throw new BookStorageException(e.getMessage());
}
}
静态语句块：
private static BookDAO bookDAO;
static {
try{
Context ctx = new InitialContext();
DataSource dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/test");
bookDAO = new BookDAO(dataSource);
}catch(Exception e) {
e.printStackTrace();
}
}
 
可能遇到的错误:javax.naming.NoInitialContextException
解决办法,jndi是在tomcat中部署的，而测试类中只有jdk环境不允许,所以需要测试的话要运行web容器