叩丁狼java学习第三天-JDBC

1.prepareStatement

// 常用方法:
void setXxx(int parameterIndex,Xxx value); //设置第几个占位符的真正参数值.
// Xxx 表示数据类型,比如 String,int,long,Date等.
void setObject(int parameterIndex, Object x); //设置第几个占位符的真正参数值.
int executeUpdate(); //执行DDL/DML语句. 注意:没有参数
// 若当前 SQL是 DDL语句,则返回 0.
// 若当前 SQL是 DML语句,则返回受影响的行数.
ResultSet executeQuery(); //执行DQL语句,返回结果集.
close(); //释放资源

2.修改JDBC使用中可能的报错问题

之前的：

        Connection connection = new JDBCUtil().loadJDBC();
        Statement st = null;

        String sql = "insert into user(username,password,email,age) value('" +
                ""+user.getUsername()+"','"+user.getPassword()+"','"+user.getEmail()+"','"+user.getAge()+"')";
        try {
            st = connection.createStatement();
            st.execute(sql);
            st.close();
            connection.close();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

修改后:

 Connection connection = new JDBCUtil().loadJDBC();
        Statement st = null;

        String sql = "insert into user(username,password,email,age) value('" +
                ""+user.getUsername()+"','"+user.getPassword()+"','"+user.getEmail()+"','"+user.getAge()+"')";
        try {
            st = connection.createStatement();
            st.execute(sql);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (st != null){
                    st.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (connection!=null){
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

主要处理了关闭的链接释放资源的问题

获取集合

// 3 获取语句对象
pst = conn.prepareStatement(sql);
// 4 执行语句操作
rs = pst.executeQuery();
// 从ResultSet 中来获取数据
while (rs.next()) {
// 获取列的数据
long resultId = rs.getLong("id");
String name = rs.getString("name");
String email = rs.getString("email");
int age = rs.getInt("age");
Student stu = new Student(resultId, name, email, age);
list.add(stu);

JDBC工具类封装

/*配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root
*/

package util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {
    static Properties properties = new Properties();

    static {
        try {
            InputStream inputStream = new FileInputStream("application.properties");
            properties.load(inputStream);
            Class.forName(properties.getProperty("driverClassName"));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection loadJDBC() {
        try {
            return DriverManager.getConnection(properties.getProperty("url"),
                    properties.getProperty("username"),
                    properties.getProperty("password"));
        } catch (Exception e) {
            System.out.println(e);
            return null;
        }
    }

    public static void closeJDBC(Statement st, Connection connection) {
        try {
            if (st != null) {
                st.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void closeJDBC(Statement st, Connection connection,ResultSet rs) {
        closeJDBC(st,connection);
        try {
            rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}