1、准备工作
new java project -> 导入 sqljdbc4.jar 包 ---> build path
2、JDBC小程序
2.1 查询 数据库中的信息
- import java.sql.*;
-
- public class TestJDBC {
- public static void main(String[] args) {
- Connection con = null;
- Statement stmt = null;
- ResultSet rs = null ;
- try{
- Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");//加载驱动程序
- String url = "jdbc:sqlserver://localhost;database = test; user = sa;password = 1234";
- con = DriverManager.getConnection(url); //创建连接对象
- stmt = con.createStatement();
- rs = stmt.executeQuery("select * from users");
- while(rs.next()){ //循环取得结果集,rs指示在最顶端
- String name = rs.getString("name");
- int psw = rs.getInt("psw");
- System.out.println(name+" "+psw);
- }
- }catch(ClassNotFoundException e){
- e.printStackTrace();
- }catch(SQLException e){
- e.printStackTrace();
- }finally{
- try{
- if(rs != null){
- rs.close();
- }
- if(stmt != null){
- stmt.close();
- }
- if(con != null){
- con.close();
- }
- }catch(SQLException e){
- e.printStackTrace();
- }
- }
- }
- }
- import java.sql.*;
- public class TestJDBC {
- public static void main(String[] args) {
- Connection con = null;
- Statement stmt = null;
- ResultSet rs = null ;
- try{
- Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");//加载驱动程序
- String url = "jdbc:sqlserver://localhost;database = test; user = sa;password = 1234";
- con = DriverManager.getConnection(url); //创建连接对象
- stmt = con.createStatement();
- rs = stmt.executeQuery("select * from users");
- while(rs.next()){ //循环取得结果集,rs指示在最顶端
- String name = rs.getString("name");
- int psw = rs.getInt("psw");
- System.out.println(name+" "+psw);
- }
- }catch(ClassNotFoundException e){
- e.printStackTrace();
- }catch(SQLException e){
- e.printStackTrace();
- }finally{
- try{
- if(rs != null){
- rs.close();
- }
- if(stmt != null){
- stmt.close();
- }
- if(con != null){
- con.close();
- }
- }catch(SQLException e){
- e.printStackTrace();
- }
- }
- }
- }
2.2 更新数据库中的信息
- import java.sql.*;
- public class TestDML {
- public static void main(String[] args) {
- Connection con = null;
- Statement stmt = null;
- try{
- Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");//加载驱动程序
- String url = "jdbc:sqlserver://localhost;database = test; user = sa;password = 1234";
- con = DriverManager.getConnection(url); //创建连接对象
- stmt = con.createStatement();
- String sql ="insert into users values('james','33332')";
- stmt.executeUpdate(sql);
- }catch(ClassNotFoundException e){
- e.printStackTrace();
- }catch(SQLException e){
- e.printStackTrace();
- }finally{
- try{
- if(stmt != null){
- stmt.close();
- }
- if(con != null){
- con.close();
- }
- }catch(SQLException e){
- e.printStackTrace();
- }
- }
- }
- }
2.3 preparedStatement 使用
- public class BallGame {
- public static void main(String[] args) {
- Connection con = null;
- PreparedStatement pstmt = null;
- try{
- Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");//加载驱动程序
- String url = "jdbc:sqlserver://localhost;database = test; user = sa;password = 1234";
- con = DriverManager.getConnection(url); //创建连接对象
- pstmt = con.prepareStatement("insert into users values(?,?)"); //占位符,简单不易出错
- pstmt.setString(1, "jordan");
- pstmt.setInt(2, 3434443);
- pstmt.executeUpdate();
- }catch(ClassNotFoundException e){
- e.printStackTrace();
- }catch(SQLException e){
- e.printStackTrace();
- }finally{
- try{
- if(pstmt != null){
- pstmt.close();
- }
- if(con != null){
- con.close();
- }
- }catch(SQLException e){
- e.printStackTrace();
- }
- }
- }
- }
2.4 callableStatement 存储过程处理 、 批处理 addBatch 、 excecuteBatch 调用
- pstmt = con.prepareStatement("insert into users values(?,?)");
- pstmt.setString(1, "jordan");
- pstmt.setInt(2, 3434443);
- pstmt.addBatch(); // 进行批处理
- pstmt.setString(1, "ruby");
- pstmt.setInt(2, 34333443);
- pstmt.addBatch();
- Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
- ResultSet.CONCUR_READ_ONLY);//要回滚的话,必须设置这两个参数
- ResultSet rs = stmt.executeQuery("select * from users");
- rs.next();
- System.out.println(rs.getString(1));
- rs.last();
- System.out.println(rs.getString(1));
- System.out.println(rs.isLast());
- System.out.println(rs.getRow());
- rs.previous();
- System.out.println(rs.getString(1));
- rs.absolute(3);
- System.out.println(rs.getString(1));
- pstmt.executeBatch();
2、5可以滚动的结果集
- Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
- ResultSet.CONCUR_READ_ONLY);//要回滚的话,必须设置这两个参数
- ResultSet rs = stmt.executeQuery("select * from users");
- rs.next();
- System.out.println(rs.getString(1));
- rs.last();
- System.out.println(rs.getString(1));
- System.out.println(rs.isLast());
- System.out.println(rs.getRow());// 共有多少条记录
- rs.previous();
- System.out.println(rs.getString(1));
- rs.absolute(3);
- System.out.println(rs.getString(1));