JDBC概述
JDBC API是一系列的接口,它统一和规范了应用程序与数据库的连接,执行SQL语句,并得到返回结果等各类操作,相关类和接口在java.sql与javax.sql包中
JDBC程序编写步骤
1.注册驱动-----加载Driver类
2.获取连接-----得到Connection
3.执行增删改查--------发送sql给mysql执行
4.释放资源---------关闭相关连接
数据库连接方式
方式一:
Driver driver = new Driver();
String url = "jdbc:mysql://bigdata12:3306/bigdata";
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","123456");
Connection connect = driver.connect(url, properties);
String sql = "insert into b(id,name) values(3,'ss')";
Statement statement = connect.createStatement();
int rows = statement.executeUpdate(sql);
System.out.println(rows > 0 ? "成功" : "失败");
statement.close();
connect.close();
Driver driver = new Driver();
String url = "jdbc:mysql://bigdata12:3306/bigdata";
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","123456");
Connection connect = driver.connect(url, properties);
System.out.println(connect);
方式二:
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");// 使用反射加载Driver类
Driver driver = (Driver)aClass.newInstance();
String url = "jdbc:mysql://bigdata12:3306/bigdata";
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","123456");
Connection connect = driver.connect(url, properties);
System.out.println(connect);
方式三:
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver)aClass.newInstance();
String url = "jdbc:mysql://bigdata12:3306/bigdata";
String user = "root";
String password = "123456";
DriverManager.registerDriver(driver);// 注册驱动
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
方式四:
// static {
// try {
// DriverManager.registerDriver(new Driver());
// } catch (SQLException var1) {
// throw new RuntimeException("Can't register driver!");
// }
// }
// 静态代码块在类加载时会执行一次
// 使用反射加载Driver类,在加载时会自动完成注册驱动
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://bigdata12:3306/bigdata";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
方式五:
1.mysql驱动5.1.6可以无需Class.forName(“com.mysql.jdbc.Driver”);
2.从jdk1.5以后使用了jdbc4,不再需要显示调用class.forName()注册驱动而是自动调用驱动jar包下META-INF\services\java.sql.Driver文本中的类名称去注册
3.建议还是写上Class.forName(“com.mysql.jdbc.Driver”)更加明确
Properties properties = new Properties();
properties.load(new FileInputStream("src/main/resources/mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
user=root
password=123456
url=jdbc:mysql://bigdata12:3306/bigdata
driver=com.mysql.jdbc.Driver
ResultSet结果集
1.表示数据库结果集的数据表,通常通过执行查询数据库的语句生成
2.ResultSet对象保持一个光标指向其当前数据行。最初,光标位于第一行之前
3.next方法将光标移动到下一行,并且由于在ResultSet对象中没有更多行时返回false,因此可以在while循环中使用循环来遍历结果集。
Properties properties = new Properties();
properties.load(new FileInputStream("src/main/resources/mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql = "select id,name from b";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
System.out.println(id + "\t" + name);
}
resultSet.close();
statement.close();
connection.close();
Statement
1.Statement对象用于执行静态SQL语句并返回其生成的结果的对象
2.在连接建立后,需要对数据库进行访问,执行命名或是SQL语句,可以通过
Statement(存在SQL注入)
PreparedStatement(预处理)
CallableStatement(存储过程)
3.Statement对象执行SQL语句,存在SQL注入风险
4.SQL注入是利用某些系统没有对用户输入的数据进行充分的检查,而在用户输入数据中注入非法的SQL语句段或命令,恶意攻击数据库
5.要防范SQL注入,只要用PreparedStatement(从Statement扩展而来)取代Statement就可以了
输入用户名:1’ or
输入密码:or ‘1’ = '1
scanner.next()当接收到空格或者单引号表示结束
scanner.nextLine();当接收到换行才表示结束
PreparedStatement
1.PreparedStatement执行的SQL语句中的参数用问号(?)来表示,调用PreparedStatement对象的setXxx()方法来设置这些参数,setXxx()方法有两个参数,第一个参数是要设置的SQL语句中的参数的索引(从1开始),第二个是设置的SQL语句中的参数的值
2.调用executeQuery(),返回ResultSet对象
3.调用executeUpdate(),执行更新包括增删改
预处理好处:
1.不再使用+拼接sql语句,减少语法错误
2.有效的解决了sql注入问题
3.大大减少了编译次数,效率较高
preparedStatement.executeQuery() 查询操作
Scanner scanner = new Scanner(System.in);
System.out.print("请输入管理员的id:");
// next()当接收到空格或者单引号表示结束
String admin_name = scanner.nextLine();
System.out.print("请输入管理员的名字:");
String admin_pwd = scanner.nextLine();
Properties properties = new Properties();
properties.load(new FileInputStream("src/main/resources/mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
String sql = "select name,pwd from admin where name =? and pwd =?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,admin_name);
preparedStatement.setString(2,admin_pwd);
// select语句执行executeQuery()
// update delete insert执行的executeUpdate()
// 这里执行的executeQuery()不要再写sql
// 如果sql里面没有?,executeQuery(sql)可以写sql
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
System.out.println("登陆成功");
} else {
System.out.println("登陆失败");
}
resultSet.close();
preparedStatement.close();
connection.close();
preparedStatement.executeUpdate() dml语句 增删改
Scanner scanner = new Scanner(System.in);
System.out.println("请输入管理员姓名:");
String admin_name = scanner.nextLine();
System.out.println("请输入管理员密码:");
String admin_pwd = scanner.nextLine();
Properties properties = new Properties();
properties.load(new FileInputStream("src/main/resources/mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Connection connection = DriverManager.getConnection(url, user, password);
String sql = "insert into admin values(?,?)";
String sql01 = "update admin set pwd = ? where name = ?";
String sql02 = "delete from admin where name = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,admin_name);
preparedStatement.setString(2,admin_pwd);
int rows = preparedStatement.executeUpdate();
System.out.println(rows > 0 ? "执行成功" : "执行失败");
preparedStatement.close();
connection.close();
JDBC相关API小结
DriverManager驱动管理类
1.getConnection(url,user,pwd)获取连接
Connection接口
1.createStatement():生成命令对象
2.prepareStatement(sql):生成预编译对象
resultSet.getXxx(“”):通过列名来获取值
resultSet.getXxx(1):获取该行的第一列
封装JDBCUtils
在jdbc操作中,获取连接和释放资源是经常使用到的,可以将其封装JDBC连接的工具类JDBCUtils
private static String user;
private static String password;
private static String url;
private static String driver;
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("src/main/resources/mysql.properties"));
user = properties.getProperty("user");
password = properties.getProperty("password");
url = properties.getProperty("url");
driver = properties.getProperty("driver");
} catch (IOException e) {
// 将编译异常转成运行异常,调用者就可以选择捕获该异常或者选择默认处理该异常
throw new RuntimeException(e);
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void close(ResultSet set, Statement statement,Connection connection){
try {
if (set != null){
set.close();
}
if (statement != null){
statement.close();
}
if (connection != null){
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
JDBCUtilsDML
Connection connection = null;
String sql = "Update actor set name = ? where id = ?";
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"周星驰");
preparedStatement.setInt(2,4);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null,preparedStatement,connection);
}
JDBCUtils查询
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
String sql = "select * from actor where id = ?";
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,5);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
String borndate = resultSet.getString("borndate");
System.out.println(id + "\t" +name +"\t" + sex +"\t" +borndate);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(resultSet,preparedStatement,connection);
}