JDBC笔记1

获取数据库连接

public void testConnection3 () throws IOException, ClassNotFoundException, SQLException {
    InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
    Properties pros = new Properties();
    pros.load(is);

    String user = pros.getProperty("user");
    String password = pros.getProperty("password");
    String url = pros.getProperty("url");
    String driverClass = pros.getProperty("driverClass");

    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection(url, user,password);
    System.out.println(conn);
}

增删改：

    public void update(String sql,Object ...args) throws Exception {

        Connection conn = null;

        PreparedStatement ps = null;

        try {

//获取数据库连接

//读取配置文件中的四个基本信息（url，uer等封装起来）

            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

            Properties pros = new Properties();

            pros.load(is);

            String user = pros.getProperty("user");

            String password = pros.getProperty("password");

            String url = pros.getProperty("url");

            String driverClass = pros.getProperty("driverClass");

//加载驱动

            Class.forName("com.mysql.jdbc.Driver");

//获取连接

            conn = DriverManager.getConnection(url, user,password);

//预编译sql语句，返回preparedstatement实例

String sql = ‘update costomer set name = ? where id = ?’;

            ps = conn.prepareStatement(sql);

//填充占位符

            for(int i=0;i< args.length;i++){

                ps.setObject(i+1,args[i]);

            }

//执行

            ps.execute();

//ps.execute

//如果执行的是查询操作，有返回结果，则此方法返回true

//如果执行的是增删改操作，没有返回结果，则此方法返回false

//ps.executeUpdate

//返回int值，成功修改的行数

    } catch (Exception e) {

        e.printStackTrace();

    } finally {

        try {

            if(ps !=null)

                ps.close();

        } catch (SQLException throwables) {

            throwables.printStackTrace();

        }

        try {

            if(conn !=null)

                conn.close();

        } catch (SQLException throwables) {

            throwables.printStackTrace();

        }

查询：

//针对不同的表的通用的查询操作，返回表中的一条记录

public class Query {

    public <T> T textQuery1(Class<T> clazz,String sql,Object...args) {

        Connection conn = null;

        PreparedStatement ps = null;

        ResultSet rs = null;

        try {

            conn = JDBCUtils.getConnection();

            ps = conn.prepareStatement(sql);

            for(int i=0;i<args.length;i++){

                ps.setObject(i,args[i]);

            }

            rs = ps.executeQuery();

            //获取结果集的元数据

            ResultSetMetaData rsmd = rs.getMetaData();

            int columnCount = rsmd.getColumnCount();

            if(rs.next()){

                T t = clazz.getDeclaredConstructor().newInstance();

                for(int i=0;i<columnCount;i++){

                    Object columnValue = rs.getObject(i + 1);

                    String columnName = rsmd.getColumnClassName(i+1);

                    Field field = clazz.getDeclaredField(columnName);

                    field.setAccessible(true);

                    field.set(t,columnValue);

                }

                return t;

            }

        } catch (Exception e) {

            e.printStackTrace();

        }finally {

            JDBCUtils.closeResource(conn,ps,rs);

        }

        return null;

    }//针对不同的表的通用的查询操作，返回表中的多条记录

public class Query {

    public <T> List<T> textQuery1(Class<T> clazz, String sql, Object...args) {

        Connection conn = null;

        PreparedStatement ps = null;

        ResultSet rs = null;

        try {

            conn = JDBCUtils.getConnection();

            ps = conn.prepareStatement(sql);

            for(int i=0;i<args.length;i++){

                ps.setObject(i,args[i]);

            }

            rs = ps.executeQuery();

            //获取结果集的元数据

            ResultSetMetaData rsmd = rs.getMetaData();

            int columnCount = rsmd.getColumnCount();

            //创建集合对象

            ArrayList<T> list = new ArrayList<>();

            while (rs.next()){

                T t = clazz.getDeclaredConstructor().newInstance();

                for(int i=0;i<columnCount;i++){

                    Object columnValue = rs.getObject(i + 1);

                    String columnName = rsmd.getColumnClassName(i+1);

                    Field field = clazz.getDeclaredField(columnName);

                    field.setAccessible(true);

                    field.set(t,columnValue);

                }

                list.add(t);

            }

            return list;

        } catch (Exception e) {

            e.printStackTrace();

        }finally {

            JDBCUtils.closeResource(conn, ps, rs);

        }

        return null;

    }

}

用替换Statement可以解决SQL注入问题

除了解决Statement的拼串、sql问题之外，PreparedStatement还可以：

1. 操作Blob数据
2. 实现更高效的批量操作

在数据表中插入Blob数据：

FileInputStream is = new FileInputStream(new File(""));

ps.setBlob(parameterindex,is);

在数据表中读取Blob数据：

Blob photo = rs.getBlob("photo");

InputStream is = photo.getBinaryStream();

FileOutputStream fos = new FileOutputStream("name");

byte[] buffer = new byte[1024];

int len;

while((len = is.read(buffer) != -1){

fos.write(buffer,0,len);

}

使用PreparedStatement实现批量数据的操作

Update、delete本身就有批量操作的效果。批量操作主要指批量插入