JDBC入门

数据库连接操作

工程文件

user=root		
password=asd.123-
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8
driverclass=com.mysql.jdbc.Driver

数据库连接

 public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.propertise.properties");
        Properties info = new Properties();
        info.load(is);
        String user = info.getProperty("user");
        String password = info.getProperty("password");
        String driverclass = info.getProperty("driverclass");
        String url = info.getProperty("url");
        Class.forName(driverclass);
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

数据库查询操作

考虑事务情况下查询单条数据

 public static <T> T select(Connection con,String sql ,Class<T> clazz,Object...args)  {
        PreparedStatement ps =null;
        try {
             ps = con.prepareStatement(sql);
            for (int i=0;i<args.length;i++)
            {
                ps.setObject(i+1,args[i]);
            }
            ResultSet rs = ps.executeQuery();
            if(rs.next())
            {
                ResultSetMetaData rsmd = rs.getMetaData();
                T t = clazz.newInstance();
                for (int i=0;i<rsmd.getColumnCount();i++)
                {
                    Object object = rs.getObject(i + 1);
                    String columnName = rsmd.getColumnName(i + 1);
					//类中属性名要与数据库中对应名字相同
                    Field declaredField = clazz.getDeclaredField(columnName);
                    declaredField.setAccessible(true);
                    declaredField.set(t,object);
                }
                return t;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            untils.untiltest.close(null,ps);		//关闭操作
        }
        return null;
    }

考虑事务情况下查询多条数据

public static <T> ArrayList<T> select(Connection con , String sql , Class<T> clazz , Object...args )  {
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            for (int i=0;i<args.length;i++)
            {
                ps.setObject(i+1,args[i]);
            }
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            ArrayList<T> list = new ArrayList();
            while(rs.next())
            {
                T t = clazz.newInstance();
                int columnCount = rsmd.getColumnCount();
                for (int i=0;i<columnCount;i++)
                {
                    Object object = rs.getObject(i + 1);
                    String columnName = rsmd.getColumnName(i + 1);
                    Field declaredField = clazz.getDeclaredField(columnName);
                    declaredField.setAccessible(true);
                    declaredField.set(t,object);
                }
                list.add(t);

            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } finally {
            untils.untiltest.close(null,ps);
        }
        return null;
    }

考虑事务情况下查询特殊数据

public static <E> E select(Connection con,String sql,Class<E> clazz,Object...args) {
        PreparedStatement ps = null;
        try {
             ps = con.prepareStatement(sql);
            for (int i=0;i<args.length;i++)
            {
                ps.setObject(i+1,args[i]);
            }
            ResultSet rs = ps.executeQuery();
            if (rs.next())
                return (E) rs.getObject(1);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            untils.untiltest.close(null,ps);
        }
        return null;
    }

数据库增删改操作

public static int update(Connection con, String sql, Object...args)  {
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            for (int i=0;i<args.length;i++)
            {
                ps.setObject(i+1,args[i]);
            }
            return ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            untils.untiltest.close(null,ps);
        }
        return 0;
    }

数据库存取图片

存入图片

public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection con = untiltest.getConnection();
        String sql = "INSERT INTO user_photo VALUES (?)";
        PreparedStatement ps = con.prepareStatement(sql);
        FileInputStream is = new FileInputStream(new File("D:\\JDBC\\src\\PrepareedStatementTest\\市场痛点2.pptx"));
        ps.setBlob(1,is);
        ps.execute();

读取图片

    InputStream is= null;
        Connection con = untiltest.getConnection();
        String sql = "select * from user_photo";
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next())
        {
            Blob blob = rs.getBlob("photo");
            is = blob.getBinaryStream();
        }


        FileOutputStream fos = new FileOutputStream(new File("D:\\JDBC\\src\\PrepareedStatementTest\\测试读取.pptx"));
        int len;
        byte[] b = new byte[20];
        while((len = is.read(b))!=-1)
        {
            fos.write(b,0,len);
        }
        is.close();
        fos.close();
        untiltest.close(con,ps);

    }

考虑事务情况下调用数据库操作

//-------采用事物的形式来更新-------------------
/*
*         DML默认情况下提交事物：（1）操作完成时 : 通过con.setAutoCommit(false);来使提交关闭
*                            （2） 程序关闭时 : 通过使用统一的连接来达成同时进行，在连接中不能关闭连接
*
*          当出现问题时候，在catch加入rollback可以实现
*
 */
 Connection con = null;		//事务中的操作使用同一个连接
        try {
            con = untiltest.getConnection();
            con.setAutoCommit(false);		//使DML不能自动提交
            String sql = "update money_test set money = money - 10 where name = ?";
            update(con,sql,"张三");
            //System.out.println(10/0);//模拟网络异常
            String sql1 = "update money_test set money = money + 10 where name = ?";
            update(con,sql1,"李四");
            con.commit();		//提交事务
        } catch (Exception e) {
            e.printStackTrace();
            //回滚数据
            try {
                con.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
         finally {
            try {
                con.setAutoCommit(true);    //使得DML可以自动提交
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

数据库隔离级别

查询数据库隔离级别

Connection con = untiltest.getConnection();
System.out.println(con.getTransactionIsolation());      //获取数据库的隔离级别

设置数据库隔离级别

Connection con = untiltest.getConnection();
con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);     //设置数据库的隔离级别