JDBC连接数据库的步骤
1、注册数据库驱动(Class.forName("..."))
2、获取数据库连接(DriverManager.getConnection(“。。。”))
3、拼写SQL语句(String sql = “。。。”)
4、创建发送语句对象:
方式一:stmtStatement = connection.createStatement();
方式二:PreparedStatement pstmt = conn.prepareStatement(sql);(预编译)
pstmt.setString(1, sname);(给占位符进行设值)
5、发送语句对象发送SQL语句并获取执行结果
方式一:(rs = stmtStatement.executeQuery(sql);)
方式二:rs = pstmt.executeQuery();(预编译)
6、处理结果集
7、关闭连接【把打开的连接依次关闭,先打开的后关闭】(rs.close(); stmtStatement.close(); connection.close();)
Connection connection = null;
Statement stmtStatement = null;
ResultSet rs = null;
try {
//1、注册数据库驱动
Class.forName("oracle.jdbc.OracleDriver");
//2、获取数据库连接
connection = DriverManager.getConnection("JDBC:oracle:thin:@localhost:1521:xe", "hr", "123");
//3、拼写SQL语句
String quary_str = "select employee_id,salary,to_char(hire_date,'YYYY-MM-DD hh24:mi:ss') hire_date from employees where department_id = 80 order by salary desc";
//4、创建发送语句对象
stmtStatement = connection.createStatement();
//5、发送语句对象发送SQL语句并获取执行结果
rs = stmtStatement.executeQuery(quary_str);
//6、处理结果
while(rs.next()){
int emp_id = rs.getInt(1);//这里是获取的表中的第一个字段
int emp_sal = rs.getInt("salary");//或者是直接获取表中的字段名称(一般使用获取名称好)
//Date emp_hire = rs.getDate("HIRE_DATE");
String emp_hire = rs.getString("hire_date");
System.out.println("员工编号:" + emp_id + "\t员工工资:" + emp_sal + "\t雇佣时间:" + emp_hire);
}
} catch (ClassNotFoundException e) {
System.out.println("连接失败1");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("连接失败2");
e.printStackTrace();
} finally{
try {
//7、关闭连接
rs.close();
stmtStatement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
或者使用占位符“?”进行预编译(避免SQL注入)
使用PreparedStatement是在创建预编译发送对象时就把sql语句带上预编译了,下一步是给占位符设值,接着执行executeQuery()方法
String sql = "select * from users where uname = ? and upsw = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, psw);
ResultSet rs = pstmt.executeQuery();