jdbc代码

1.jdbcutiul的代码，

  1 package gz.itcast.util;
  2 
  3 import java.io.InputStream;
  4 import java.sql.Connection;
  5 import java.sql.DriverManager;
  6 import java.sql.ResultSet;
  7 import java.sql.SQLException;
  8 import java.sql.Statement;
  9 import java.util.Properties;
 10 
 11 /**
 12  * jdbc工具类
 13  * @author APPle
 14  *
 15  */
 16 public class JdbcUtil {
 17     private static String url = null;
 18     private static String user = null;
 19     private static String password = null;
 20     private static String driverClass = null;
 21     
 22     /**
 23      * 静态代码块中（只加载一次）
 24      */
 25     static{
 26         try {
 27             //读取db.properties文件
 28             Properties props = new Properties();
 29             /**
 30              *  . 代表java命令运行的目录
 31              *  在java项目下，. java命令的运行目录从项目的根目录开始
 32              *  在web项目下，  . java命令的而运行目录从tomcat/bin目录开始
 33              *  所以不能使用点.
 34              */
 35             //FileInputStream in = new FileInputStream("./src/db.properties");
 36             
 37             /**
 38              * 使用类路径的读取方式
 39              *  / : 斜杠表示classpath的根目录
 40              *     在java项目下，classpath的根目录从bin目录开始
 41              *     在web项目下，classpath的根目录从WEB-INF/classes目录开始
 42              */
 43             InputStream in = JdbcUtil.class.getResourceAsStream("/db.properties");
 44             
 45             //加载文件
 46             props.load(in);
 47             //读取信息
 48             url = props.getProperty("url");
 49             user = props.getProperty("user");
 50             password = props.getProperty("password");
 51             driverClass = props.getProperty("driverClass");
 52             
 53             
 54             //注册驱动程序
 55             Class.forName(driverClass);
 56         } catch (Exception e) {
 57             e.printStackTrace();
 58             System.out.println("驱程程序注册出错");
 59         }
 60     }
 61 
 62     /**
 63      * 抽取获取连接对象的方法
 64      */
 65     public static Connection getConnection(){
 66         try {
 67             Connection conn = DriverManager.getConnection(url, user, password);
 68             return conn;
 69         } catch (SQLException e) {
 70             e.printStackTrace();
 71             throw new RuntimeException(e);
 72         }
 73     }
 74     
 75     
 76     /**
 77      * 释放资源的方法
 78      */
 79     public static void close(Connection conn,Statement stmt){
 80         if(stmt!=null){
 81             try {
 82                 stmt.close();
 83             } catch (SQLException e) {
 84                 e.printStackTrace();
 85                 throw new RuntimeException(e);
 86             }
 87         }
 88         if(conn!=null){
 89             try {
 90                 conn.close();
 91             } catch (SQLException e) {
 92                 e.printStackTrace();
 93                 throw new RuntimeException(e);
 94             }
 95         }
 96     }
 97     
 98     public static void close(Connection conn,Statement stmt,ResultSet rs){
 99         if(rs!=null)
100             try {
101                 rs.close();
102             } catch (SQLException e1) {
103                 e1.printStackTrace();
104                 throw new RuntimeException(e1);
105             }
106         if(stmt!=null){
107             try {
108                 stmt.close();
109             } catch (SQLException e) {
110                 e.printStackTrace();
111                 throw new RuntimeException(e);
112             }
113         }
114         if(conn!=null){
115             try {
116                 conn.close();
117             } catch (SQLException e) {
118                 e.printStackTrace();
119                 throw new RuntimeException(e);
120             }
121         }
122     }
123 }

2.静态sql查询

2.1建表

  1 package gz.itcast.util;
  2 
  3 import java.io.InputStream;
  4 import java.sql.Connection;
  5 import java.sql.DriverManager;
  6 import java.sql.ResultSet;
  7 import java.sql.SQLException;
  8 import java.sql.Statement;
  9 import java.util.Properties;
 10 
 11 /**
 12  * jdbc工具类
 13  * @author APPle
 14  *
 15  */
 16 public class JdbcUtil {
 17     private static String url = null;
 18     private static String user = null;
 19     private static String password = null;
 20     private static String driverClass = null;
 21     
 22     /**
 23      * 静态代码块中（只加载一次）
 24      */
 25     static{
 26         try {
 27             //读取db.properties文件
 28             Properties props = new Properties();
 29             /**
 30              *  . 代表java命令运行的目录
 31              *  在java项目下，. java命令的运行目录从项目的根目录开始
 32              *  在web项目下，  . java命令的而运行目录从tomcat/bin目录开始
 33              *  所以不能使用点.
 34              */
 35             //FileInputStream in = new FileInputStream("./src/db.properties");
 36             
 37             /**
 38              * 使用类路径的读取方式
 39              *  / : 斜杠表示classpath的根目录
 40              *     在java项目下，classpath的根目录从bin目录开始
 41              *     在web项目下，classpath的根目录从WEB-INF/classes目录开始
 42              */
 43             InputStream in = JdbcUtil.class.getResourceAsStream("/db.properties");
 44             
 45             //加载文件
 46             props.load(in);
 47             //读取信息
 48             url = props.getProperty("url");
 49             user = props.getProperty("user");
 50             password = props.getProperty("password");
 51             driverClass = props.getProperty("driverClass");
 52             
 53             
 54             //注册驱动程序
 55             Class.forName(driverClass);
 56         } catch (Exception e) {
 57             e.printStackTrace();
 58             System.out.println("驱程程序注册出错");
 59         }
 60     }
 61 
 62     /**
 63      * 抽取获取连接对象的方法
 64      */
 65     public static Connection getConnection(){
 66         try {
 67             Connection conn = DriverManager.getConnection(url, user, password);
 68             return conn;
 69         } catch (SQLException e) {
 70             e.printStackTrace();
 71             throw new RuntimeException(e);
 72         }
 73     }
 74     
 75     
 76     /**
 77      * 释放资源的方法
 78      */
 79     public static void close(Connection conn,Statement stmt){
 80         if(stmt!=null){
 81             try {
 82                 stmt.close();
 83             } catch (SQLException e) {
 84                 e.printStackTrace();
 85                 throw new RuntimeException(e);
 86             }
 87         }
 88         if(conn!=null){
 89             try {
 90                 conn.close();
 91             } catch (SQLException e) {
 92                 e.printStackTrace();
 93                 throw new RuntimeException(e);
 94             }
 95         }
 96     }
 97     
 98     public static void close(Connection conn,Statement stmt,ResultSet rs){
 99         if(rs!=null)
100             try {
101                 rs.close();
102             } catch (SQLException e1) {
103                 e1.printStackTrace();
104                 throw new RuntimeException(e1);
105             }
106         if(stmt!=null){
107             try {
108                 stmt.close();
109             } catch (SQLException e) {
110                 e.printStackTrace();
111                 throw new RuntimeException(e);
112             }
113         }
114         if(conn!=null){
115             try {
116                 conn.close();
117             } catch (SQLException e) {
118                 e.printStackTrace();
119                 throw new RuntimeException(e);
120             }
121         }
122     }
123 }

2.2插入数据

package gz.itcast.b_statement;

import gz.itcast.util.JdbcUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

/**
 * 使用Statement执行DML语句
 * @author APPle
 *
 */
public class Demo2 {
    private String url = "jdbc:mysql://localhost:3306/day17";
    private String user = "root";
    private String password = "root";

    /**
     * 增加
     */
    @Test
    public void testInsert(){
        Connection conn = null;
        Statement stmt = null;
        try {
            //通过工具类获取连接对象
            conn = JdbcUtil.getConnection();
            
            //3.创建Statement对象
            stmt = conn.createStatement();
            
            //4.sql语句
            String sql = "INSERT INTO student(NAME,gender) VALUES('李四','女')";
            
            //5.执行sql
            int count = stmt.executeUpdate(sql);
            
            System.out.println("影响了"+count+"行");
            
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally{
            //关闭资源
            /*if(stmt!=null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }*/
            JdbcUtil.close(conn, stmt);
        }
    }
    
    /**
     * 修改
     */
    @Test
    public void testUpdate(){
        Connection conn = null;
        Statement stmt = null;
        //模拟用户输入
        String name = "陈六";
        int id = 3;
        try {
            /*//1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            
            //2.获取连接对象
            conn = DriverManager.getConnection(url, user, password);*/
            //通过工具类获取连接对象
            conn = JdbcUtil.getConnection();
            
            //3.创建Statement对象
            stmt = conn.createStatement();
            
            //4.sql语句
            String sql = "UPDATE student SET NAME='"+name+"' WHERE id="+id+"";
            
            System.out.println(sql);
            
            //5.执行sql
            int count = stmt.executeUpdate(sql);
            
            System.out.println("影响了"+count+"行");
            
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally{
            //关闭资源
            /*if(stmt!=null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }*/
            JdbcUtil.close(conn, stmt);
        }
    }
    
    /**
     * 删除
     */
    @Test
    public void testDelete(){
        Connection conn = null;
        Statement stmt = null;
        //模拟用户输入
        int id = 3;
        try {
            /*//1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            
            //2.获取连接对象
            conn = DriverManager.getConnection(url, user, password);*/
            //通过工具类获取连接对象
            conn = JdbcUtil.getConnection();
            
            //3.创建Statement对象
            stmt = conn.createStatement();
            
            //4.sql语句
            String sql = "DELETE FROM student WHERE id="+id+"";
            
            System.out.println(sql);
            
            //5.执行sql
            int count = stmt.executeUpdate(sql);
            
            System.out.println("影响了"+count+"行");
            
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally{
            //关闭资源
            /*if(stmt!=null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }*/
            JdbcUtil.close(conn, stmt);
        }
    }
}

2.3查询数据

package gz.itcast.b_statement;

import gz.itcast.util.JdbcUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

/**
 * 使用Statement执行DQL语句（查询操作）
 * @author APPle
 *
 */
public class Demo3 {

    @Test
    public void test1(){
        Connection conn = null;
        Statement stmt = null;
        try{
            //获取连接
            conn = JdbcUtil.getConnection();
            //创建Statement
            stmt = conn.createStatement();
            //准备sql
            String sql = "SELECT * FROM student";
            //执行sql
            ResultSet rs = stmt.executeQuery(sql);
            
            //移动光标
            /*boolean flag = rs.next();
            
            flag = rs.next();
            flag = rs.next();
            if(flag){
                //取出列值
                //索引
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String gender = rs.getString(3);
                System.out.println(id+","+name+","+gender);
                
                //列名称
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                System.out.println(id+","+name+","+gender);
            }*/
            
            //遍历结果
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                System.out.println(id+","+name+","+gender);
            }
            
        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            JdbcUtil.close(conn, stmt);
        }
    }
}

3.动态SQL查询

3.1更新

package gz.itcast.c_prepared;

import gz.itcast.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.Test;
/**
 * PreparedStatement執行sql語句
 * @author APPle
 *
 */
public class Demo1 {

    /**
     * 增加
     */
    @Test
    public void testInsert() {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            //1.获取连接
            conn = JdbcUtil.getConnection();
            
            //2.准备预编译的sql
            String sql = "INSERT INTO student(NAME,gender) VALUES(?,?)"; //?表示一个参数的占位符
            
            //3.执行预编译sql语句(检查语法)
            stmt = conn.prepareStatement(sql);
            
            //4.设置参数值
            /**
             * 参数一： 参数位置  从1开始
             */
            stmt.setString(1, "李四");
            stmt.setString(2, "男");
            
            //5.发送参数，执行sql
            int count = stmt.executeUpdate();
            
            System.out.println("影响了"+count+"行");
            
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.close(conn, stmt);
        }
    }
    
    /**
     * 修改
     */
    @Test
    public void testUpdate() {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            //1.获取连接
            conn = JdbcUtil.getConnection();
            
            //2.准备预编译的sql
            String sql = "UPDATE student SET NAME=? WHERE id=?"; //?表示一个参数的占位符
            
            //3.执行预编译sql语句(检查语法)
            stmt = conn.prepareStatement(sql);
            
            //4.设置参数值
            /**
             * 参数一： 参数位置  从1开始
             */
            stmt.setString(1, "王五");
            stmt.setInt(2, 9);
            
            //5.发送参数，执行sql
            int count = stmt.executeUpdate();
            
            System.out.println("影响了"+count+"行");
            
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.close(conn, stmt);
        }
    }
    
    /**
     * 删除
     */
    @Test
    public void testDelete() {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            //1.获取连接
            conn = JdbcUtil.getConnection();
            
            //2.准备预编译的sql
            String sql = "DELETE FROM student WHERE id=?"; //?表示一个参数的占位符
            
            //3.执行预编译sql语句(检查语法)
            stmt = conn.prepareStatement(sql);
            
            //4.设置参数值
            /**
             * 参数一： 参数位置  从1开始
             */
            stmt.setInt(1, 9);
            
            //5.发送参数，执行sql
            int count = stmt.executeUpdate();
            
            System.out.println("影响了"+count+"行");
            
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.close(conn, stmt);
        }
    }
    
    /**
     * 查询
     */
    @Test
    public void testQuery() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            //1.获取连接
            conn = JdbcUtil.getConnection();
            
            //2.准备预编译的sql
            String sql = "SELECT * FROM student"; 
            
            //3.预编译
            stmt = conn.prepareStatement(sql);
            
            //4.执行sql
            rs = stmt.executeQuery();
            
            //5.遍历rs
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                System.out.println(id+","+name+","+gender);
            }
            
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            //关闭资源
            JdbcUtil.close(conn,stmt,rs);
        }
    }
}

3.2查询

package gz.itcast.c_prepared;

import gz.itcast.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

/**
 * 模拟用户登录效果
 * @author APPle
 *
 */
public class Demo2 {
    //模拟用户输入
    //private String name = "ericdfdfdfddfd' OR 1=1 -- ";
    private String name = "eric";
    //private String password = "123456dfdfddfdf";
    private String password = "123456";

    /**
     * Statment存在sql被注入的风险
     */
    @Test
    public void testByStatement(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //获取连接
            conn = JdbcUtil.getConnection();
            
            //创建Statment
            stmt = conn.createStatement();
            
            //准备sql
            String sql = "SELECT * FROM users WHERE NAME='"+name+"' AND PASSWORD='"+password+"'";
            
            //执行sql
            rs = stmt.executeQuery(sql);
            
            if(rs.next()){
                //登录成功
                System.out.println("登录成功");
            }else{
                System.out.println("登录失败");
            }
            
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.close(conn, stmt ,rs);
        }
        
    }
    
    /**
     * PreparedStatement可以有效地防止sql被注入
     */
    @Test
    public void testByPreparedStatement(){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            //获取连接
            conn = JdbcUtil.getConnection();
            
            String sql = "SELECT * FROM users WHERE NAME=? AND PASSWORD=?";
            
            //预编译
            stmt = conn.prepareStatement(sql);
            
            //设置参数
            stmt.setString(1, name);
            stmt.setString(2, password);
            
            //执行sql
            rs = stmt.executeQuery();
            
            if(rs.next()){
                //登录成功
                System.out.println("登录成功");
            }else{
                System.out.println("登录失败");
            }
            
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.close(conn, stmt ,rs);
        }
        
    }
}

4.执行存储过程

package gz.itcast.d_callable;

import gz.itcast.util.JdbcUtil;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

import org.junit.Test;

/**
 * 使用CablleStatement调用存储过程
 * @author APPle
 *
 */
public class Demo1 {

    /**
     * 调用带有输入参数的存储过程
     * CALL pro_findById(4);
     */
    @Test
    public void test1(){
        Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            //获取连接
            conn = JdbcUtil.getConnection();
            
            //准备sql
            String sql = "CALL pro_findById(?)"; //可以执行预编译的sql
            
            //预编译
            stmt = conn.prepareCall(sql);
            
            //设置输入参数
            stmt.setInt(1, 6);
            
            //发送参数
            rs = stmt.executeQuery(); //注意： 所有调用存储过程的sql语句都是使用executeQuery方法执行！！！
            
            //遍历结果
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                System.out.println(id+","+name+","+gender);
            }
            
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.close(conn, stmt ,rs);
        }
    }
    
    /**
     * 执行带有输出参数的存储过程
     * CALL pro_findById2(5,@NAME);
     */
    @Test
    public void test2(){
        Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            //获取连接
            conn = JdbcUtil.getConnection();
            //准备sql
            String sql = "CALL pro_findById2(?,?)"; //第一个？是输入参数，第二个？是输出参数
            
            //预编译
            stmt = conn.prepareCall(sql);
            
            //设置输入参数
            stmt.setInt(1, 6);
            //设置输出参数(注册输出参数)
            /**
             * 参数一： 参数位置
             * 参数二： 存储过程中的输出参数的jdbc类型    VARCHAR(20)
             */
            stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
            
            //发送参数，执行
            stmt.executeQuery(); //结果不是返回到结果集中，而是返回到输出参数中
            
            //得到输出参数的值
            /**
             * 索引值： 预编译sql中的输出参数的位置
             */
            String result = stmt.getString(2); //getXX方法专门用于获取存储过程中的输出参数
            
            System.out.println(result);

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.close(conn, stmt ,rs);
        }
    }
}

posted @ 2016-06-14 21:48 仁波切阅读(267) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

仁波切

空想不做，万事东流水！实践想法，积流成海！

jdbc代码

公告

仁波切

空想不做，万事东流水！ 实践想法，积流成海！

jdbc代码

公告

空想不做，万事东流水！实践想法，积流成海！