JDBC学习（ORM）

/**
 * 测试简历连接
 */
public class Demo01 {
    public static void main(String[] args) {
        //加载驱动类
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3305/testjdbc";
            String user="root";
            String pwd="root";
            Connection conn=DriverManager.getConnection(url,user,pwd);
            Statement stmt=conn.createStatement();
//            String sql="insert into t_user(username,pwd,regTime) values('lsd',6666,now())";
//            boolean flag=stmt.execute(sql);
            //sql注入
            String id="6 or 1=1";
            String sql="delete from t_user where id='"+id+"'";
            System.out.println(conn);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

prepareStatement：

/**
 * @author lsd
 * @create 2019-06-10 14:02
 */
public class Demo02 {
    public static void main(String[] args) {
        //加载驱动类
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3305/testjdbc";
            String user="root";
            String pwd="root";
            Connection conn= DriverManager.getConnection(url,user,pwd);
            String sql="insert into t_user(username,pwd,regTime) values(?,?,?)";
            PreparedStatement ps=conn.prepareStatement(sql);
//            ps.setString(1, "李胜达1");
//            ps.setString(2, "1111");
//            ps.setDate(3,new Date(System.currentTimeMillis()));
            ps.setObject(1, "李胜达2");
            ps.setObject(2, "2222");
            ps.setObject(3,new Date(System.currentTimeMillis()));
            ps.execute();
            System.out.println(conn);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

/**
 * @author lsd
 * @create 2019-06-10 14:02
 */
public class Demo03 {
    public static void main(String[] args) {
        //加载驱动类
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3305/testjdbc";
            String user="root";
            String pwd="root";
            Connection conn= DriverManager.getConnection(url,user,pwd);
            String sql="select * from t_user";
            PreparedStatement ps=conn.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                Integer id=rs.getInt(1);
                String username=rs.getString(2);
                String password=rs.getString(3);
                Date date=rs.getDate(4);
                System.out.println(id+"  "+username+"  "+password+"  "+date);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

/**
 * @author lsd
 * @create 2019-06-10 14:02
 */
public class Demo03 {
    public static void main(String[] args) {
        //加载驱动类
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3305/testjdbc";
            String user="root";
            String pwd="root";
            Connection conn= DriverManager.getConnection(url,user,pwd);
            String sql="select * from t_user";
            PreparedStatement ps=conn.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                Integer id=rs.getInt(1);
                String username=rs.getString(2);
                String password=rs.getString(3);
                Date date=rs.getDate(4);
                System.out.println(id+"  "+username+"  "+password+"  "+date);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

/**
 * Batch 批处理 尽量使用statement 不然容易出异常，预编译空间比较夏普
 * @author lsd
 * @create 2019-06-10 14:02
 */
public class Demo04 {
    public static void main(String[] args) {
        //加载驱动类
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3305/testjdbc";
            String user="root";
            String pwd="root";
            Connection conn= DriverManager.getConnection(url,user,pwd);
            conn.setAutoCommit(false);
            Statement stmt=conn.createStatement();
            for (int i = 0; i < 20000; i++) {
                stmt.addBatch("insert into t_user(username,pwd,regTime) values('gao"+i+"',6666,now())");
            }
            stmt.executeBatch();
            conn.commit();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

考虑权限问题，需要了解脏读，不可重复读，幻读的原理。

/**
 * 测试事务
 * @author lsd
 * @create 2019-06-10 14:02
 */
public class Demo05 {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        //加载驱动类
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3305/testjdbc";
            String user="root";
            String pwd="root";
            conn= DriverManager.getConnection(url,user,pwd);
            //默认是true
            conn.setAutoCommit(false);
            String sql="insert into t_user(username,pwd,regTime) values(?,?,?)";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1, "李胜达1");
            ps.setString(2, "1111");
            ps.setDate(3,new Date(System.currentTimeMillis()));
            ps.execute();
            System.out.println("插入一条记录");
            Thread.sleep(6000);
            sql="insert into t_user(username,pwd) values(?,?,?)";
            PreparedStatement ps2=conn.prepareStatement(sql);
            ps2.setString(1, "李胜达1");
            ps2.setString(2, "1111");
            ps2.execute();
            conn.commit();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }
}

事务：一个成功则都成功，不然都失败，默认事务隔离为读提交。

日期处理：

/**
 * 测试事务
 * @author lsd
 * @create 2019-06-10 14:02
 */
public class Demo06 {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        //加载驱动类
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3305/testjdbc";
            String user="root";
            String pwd="root";
            conn= DriverManager.getConnection(url,user,pwd);
            //默认是true
            String sql="insert into t_user(username,pwd,regTime,lastTime) values(?,?,?,?)";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1, "李胜达1");
            ps.setString(2, "1111");
            Date date=new Date(System.currentTimeMillis());
            ps.setDate(3,date);
            Timestamp timestamp=new Timestamp(System.currentTimeMillis());
            ps.setTimestamp(4,timestamp );
            ps.execute();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

/**
 * 测试时间处理
 * @author lsd
 * @create 2019-06-10 14:02
 */
public class Demo07 {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        //加载驱动类
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3305/testjdbc";
            String user="root";
            String pwd="root";
            conn= DriverManager.getConnection(url,user,pwd);
            //默认是true
            String sql="select * from t_user where regTime> ? and regTime<?";

            PreparedStatement ps=conn.prepareStatement(sql);
            Date start=new Date(strDate("2019-06-01 00:00:00"));
            Date end=new Date(strDate("2019-06-12 00:00:00"));
            ps.setDate(1,start);
            ps.setDate(2,end);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                System.out.print(rs.getInt(1)+" ");
                System.out.print(rs.getString(2)+" ");
                System.out.print(rs.getString(3)+" ");
                System.out.print(rs.getDate(4)+" ");
                System.out.println(rs.getTimestamp(5));
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 将字符串转成对应的long类型
     * @param dateStr
     * @return
     */
    public static long strDate(String dateStr){
        SimpleDateFormat format=new SimpleDateFormat("yy-MM-dd hh:mm:ss");
        try {
            return format.parse(dateStr).getTime();
        } catch (ParseException e) {
            e.printStackTrace();
            return 0;
        }
    }
}

/**
 * 操作text类型字段Clob（文本大对象）
 * @author lsd
 * @create 2019-06-10 14:02
 */
public class Demo08 {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        //加载驱动类
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3305/testjdbc";
            String user="root";
            String pwd="root";
            conn= DriverManager.getConnection(url,user,pwd);
            //默认是true
            String sql="insert into t_user(username,myInfo) values(?,?)";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,"lsd");
//            ps.setClob(2,new FileReader(new File("d:/a.txt")));
            ps.setClob(2, new BufferedReader(new InputStreamReader(new ByteArrayInputStream("test".getBytes()))));
            ps.execute();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
Clob clob = rs.getClob("remark");//java.sql.Clob
    String value="";
    String line="";
    if(clob!=null){
           Reader reader=((oracle.sql.CLOB)clob).getCharacterStream();
           BufferedReader br=new BufferedReader(reader);
           while((line=br.readLine())!=null)
           {
            value += line + "\r\n";
           }

    } 

FileOutputStream outputStream = new FileOutputStream("/Users/liuwei/temp.png");
            InputStream inputStream = results.getBinaryStream(1);
            int num = -1;
            while((num=inputStream.read())!=-1) {
                outputStream.write(num);
            }
            outputStream.flush();
            inputStream.close();
            outputStream.close();

/**
 * Blob（二进制大对象）
 * @author lsd
 * @create 2019-06-10 14:02
 */
public class Demo08 {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        //加载驱动类
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3305/testjdbc";
            String user="root";
            String pwd="root";
            conn= DriverManager.getConnection(url,user,pwd);
            //默认是true
            String sql="insert into t_user(username,headLong) values(?,?)";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,"lsd");
            ps.setBlob(2,new FileInputStream("d:/a.txt"));
            ps.execute();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }
}

/**
 * 打开sql连接和关闭连接的工具类
 * @author lsd
 * @create 2019-06-10 18:53
 */
public class JDBCUtil {
    public static Connection getMysqlConnection(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3305/testjdbc";
            String user="root";
            String pwd="root";
            return DriverManager.getConnection(url,user,pwd);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    public static void  close(ResultSet rs, Connection conn, Statement stmt){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

/**
 * 打开sql连接和关闭连接的工具类
 * @author lsd
 * @create 2019-06-10 18:53
 */
public class JDBCUtil {
    //读取和处理资源文件信息
    static Properties prop=null;
    static{
        prop=new Properties();
        try {
            prop.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static Connection getMysqlConnection(){
        try {
            Class.forName(prop.getProperty("Driver"));
            String url=prop.getProperty("Url");
            String user=prop.getProperty("yser");
            String pwd=prop.getProperty("pwd");
            return DriverManager.getConnection(url,user,pwd);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    public static void  close(ResultSet rs, Connection conn, Statement stmt){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}