JDBC
JDBC是一种连接JAVA和mysql的工具,后面又mybatis框架会使用到他所以兄弟们,学!
JDBC的5中连接方式
第一种 使用driver
public void m1() throws SQLException {
//创建一个Dirver注册驱动
Driver driver = new Driver ();
//固定写法mysql可以换成其他数据库 java2022是数据库的名字
String url = "jdbc:mysql://localhost:3306/java2022";
Properties properties = new Properties ();
properties.setProperty ("user", "root");
properties.setProperty ("password", "123456");
//灵魂一步drivet。connet返回一个connect连接
Connection connect = driver.connect (url, properties);
//statement是真正操作数据库的使用connect连接创建一个statement
Statement statement = connect.createStatement ();
//后续先不用看
String path = "update news set newrong = 'newrong' where id = 1";
int i = statement.executeUpdate (path);
System.out.println (i>0 ? "cg" : "sb");
statement.close ();
connect.close ();
}
第二种 使用class.forname来动态选择
@Test
public void m2() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
Class<?> aClass = Class.forName ("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance ();
String url = "jdbc:mysql://localhost:3306/java2022";
Properties properties = new Properties ();
properties.setProperty ("user", "root");
properties.setProperty ("password", "123456");
Connection connect = driver.connect (url, properties);
Statement statement = connect.createStatement ();
String path = "update news set newrong = 'newrong' where id = 1";
int i = statement.executeUpdate (path);
System.out.println (i>0 ? "cg" : "sb");
statement.close ();
connect.close ();
}
第三种 使用DriverManager
@Test
public void m3() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
Class<?> aClass = Class.forName ("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance ();
String url = "jdbc:mysql://localhost:3306/java2022";
Properties properties = new Properties ();
properties.setProperty ("user", "root");
properties.setProperty ("password", "123456");
DriverManager.deregisterDriver (driver);
Connection connect = DriverManager.getConnection (url, properties);
Statement statement = connect.createStatement ();
String path = "update news set newrong = 'newrong' where id = 1";
int i = statement.executeUpdate (path);
System.out.println (i>0 ? "cg" : "sb");
statement.close ();
connect.close ();
}
第四种 使用Class.forname
@Test
public void m4() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
//底层自动注册(有一个静态方法) 所以可以只写这一个方法
Class.forName ("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/java2022";
String user = "root";
String password = "123456";
Connection connect = DriverManager.getConnection (url, user,password);
Statement statement = connect.createStatement ();
String path = "update news set newrong = 'newrong' where id = 1";
int i = statement.executeUpdate (path);
System.out.println (i>0 ? "cg" : "sb");
statement.close ();
connect.close ();
}
第五种使用properties文件进行填充
@Test
public void m5() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException {
Class.forName ("com.mysql.jdbc.Driver");
Properties properties = new Properties ();
properties.load (new FileInputStream ("src\\com\\class3\\class3_22\\db.properties"));
String url = properties.getProperty ("url");
String user = properties.getProperty ("user");
String password = properties.getProperty ("password");
Connection connect = DriverManager.getConnection (url, user,password);
Statement statement = connect.createStatement ();
String path = "update news set newrong = 'newrong' where id = 1";
int i = statement.executeUpdate (path);
System.out.println (i>0 ? "cg" : "sb");
statement.close ();
connect.close ();
}
- 注:再mysql4之后(包括mysql4)我们可以不用写calss.forname()进行驱动了,已经自动注册过了但是还是建议写上
ResultSet
那么就有小伙伴就说那Select * from table 会返回一大堆结果呢,java中怎么返回呀,放心java是一个成熟的大姐姐了会有处理办法的
ResultSet就是他的方法其中的row数组下的Object数组 中存储的每一行数据
让我们举个例子吧
@Test
public void m1() throws ClassNotFoundException, IOException, SQLException {
Class.forName ("com.mysql.jdbc.Driver");
Properties properties = new Properties ();
properties.load (new FileInputStream ("src\\com\\class3\\class3_22\\db.properties"));
String url = properties.getProperty ("url");
String user = properties.getProperty ("user");
String password = properties.getProperty ("password");
Connection connect = DriverManager.getConnection (url, user, password);
Statement statement = connect.createStatement ();
String sql = "select * from news";
ResultSet resultSet = statement.executeQuery (sql);
while (resultSet.next ()) {
int anInt = resultSet.getInt (1);
String string = resultSet.getString (2);
System.out.println (anInt + "\t" + string);
}
resultSet.close ();
statement.close ();
connect.close ();
}
其中resultSet.getInt(1) 中的1表示的是第1列 , news数据库中第一列为id为int类型 第二列为varchar()类型 所以第二列为String别忘了close resultSet哦
Statement
一般情况下,我们不会在工作中使用Statement因为它存在sql注入问题
所以我们来介绍PreparedStatement
preparedStatement
完美的解决了sql注入问题
Scanner sca = new Scanner(System.in);
System.out.print ("请输入账号:");
Integer localhost = sca.nextInt ();
System.out.print ("请输入密码:");
String localhostpasword = sca.next ();
Class.forName ("com.mysql.jdbc.Driver");
Properties properties = new Properties ();
properties.load (new FileInputStream ("src\\com\\class3\\class3_22\\db.properties"));
String user = properties.getProperty ("user");
String password = properties.getProperty ("password");
String url = properties.getProperty ("url");
Connection connection = DriverManager.getConnection (url, user, password);
String sql = "select * from news where id = ? and newrong = ?";
PreparedStatement preparedStatement = connection.prepareStatement (sql);
preparedStatement.setInt (1 , localhost);
preparedStatement.setString (2, localhostpasword);
ResultSet resultSet = preparedStatement.executeQuery ();
if (resultSet.next ()){
System.out.println ("恭喜你成功登录");
}else {
System.out.println ("很遗憾你登录失败");
}
}
其中需要注意的是executeQuery()后的参数就不用写了 ,因为在前面已经被处理了,这里不仅画蛇添足还是错误的,非常好用~
总结一下JDBCAPI
封装一个JDCBCUtil工具类
public class JDBCUtils {
private static String url;
private static String password;
private static String user;
private static String driver;
static {
Properties properties = new Properties ();
try {
properties.load (new FileInputStream ("src\\com\\class3\\class3_22\\db.properties"));
url = properties.getProperty ("url");
password = properties.getProperty ("password");
user = properties.getProperty ("user");
driver = properties.getProperty ("driver");
} catch (IOException e) {
throw new RuntimeException ();
}
}
public static Connection coonect() {
try {
Class.forName (driver);
return DriverManager.getConnection (url, user, password);
} catch (Exception e) {
throw new RuntimeException ();
}
}
public static void close( ResultSet resultSet , Statement statement ,Connection connection ){
try {
if (statement != null){
statement.close ();
}
if (connection != null){
connection.close ();
}
if (resultSet != null){
resultSet.close ();
}
} catch (SQLException e) {
e.printStackTrace ();
}
}
}
可以多使用自己写的工具这是写dml时候的代码
@Test
public void m1(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.coonect ();
String sql = "update admin set name = ? where id = ?";
preparedStatement = connection.prepareStatement (sql);
preparedStatement.setString (1 , "xiahui");
preparedStatement.setInt (2 , 1);
int i = preparedStatement.executeUpdate ();
System.out.println (i > 0 ?"成功" : "失败");
} catch (SQLException e) {
e.printStackTrace ();
}finally {
JDBCUtils.close (null , preparedStatement , connection);
}
}
使用查询语句时的情况
@Test
public void m1() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet set = null;
try {
connection = JDBCUtils.coonect ();
String sql = "SELECT * from admin";
preparedStatement = connection.prepareStatement (sql);
set = preparedStatement.executeQuery ();
while (set.next ()) {
int id = set.getInt ("id");
String name = set.getString ("name");
System.out.println (id + "\t" + name);
}
} catch (SQLException e) {
e.printStackTrace ();
} finally {
JDBCUtils.close (set, preparedStatement, connection);
}
}
事务处理
其中connect.setAutoCommit() true 表示自动提交 false 表示取消自动提交
public static void main( String[] args ){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet set = null;
try {
connection = JDBCUtils.coonect ();
String sql = "UPDATE `user` set passwod = passwod - 1 where id = 1";
String sql1 = "UPDATE `user` set passwod = passwod + 1 where id = 2";
connection.setAutoCommit (false);
preparedStatement = connection.prepareStatement (sql);
preparedStatement.executeUpdate ();
PreparedStatement preparedStatement1 = connection.prepareStatement (sql1);
preparedStatement1.executeUpdate ();
connection.commit ();
} catch (SQLException e) {
try {
connection.rollback ();
} catch (SQLException e1) {
e1.printStackTrace ();
}
e.printStackTrace ();
} finally {
JDBCUtils.close (set, preparedStatement, connection);
}
}
不要忘记再catch时使用rollback来回滚操作,commit来手动提交
批处理
批处理
有一辆小车 ,有5000个小朋友,要从a到b 怎么样才能快速呢,按传统方法一次小车车只载一个小朋友要载5000次
代码如下:
@Test
public void m1(){
Connection coonect = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
coonect = JDBCUtils.coonect ();
String sql = "insert into `user` values(? , ? , '666')";
preparedStatement = coonect.prepareStatement (sql);
long l = System.currentTimeMillis ();
for (int i = 0; i < 5000; i++) {
preparedStatement.setInt (1,i);
preparedStatement.setString (2 , "name");
preparedStatement.executeUpdate ();
}
long l1 = System.currentTimeMillis ();
System.out.println ("使用时间:" + (l1 - l));
} catch (Exception e) {
e.printStackTrace ();
}finally {
JDBCUtils.close (resultSet , preparedStatement , coonect);
}
}
运行结果如下:
一共运行了9秒 , 十分难受 ,
让我们换一个思路 , 1辆车(特别特别大哦) 我说一个数 ! 载5000个!
使用批处理代码如下
:
@Test
public void m1(){
Connection coonect = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
coonect = JDBCUtils.coonect ();
String sql = "insert into `user` values(? , ? , '666')";
preparedStatement = coonect.prepareStatement (sql);
long l = System.currentTimeMillis ();
for (int i = 0; i < 5000; i++) {
preparedStatement.setInt (1,i);
preparedStatement.setString (2 , "name");
preparedStatement.addBatch ();
if ((i+1) % 1000 == 0){
preparedStatement.executeBatch ();
preparedStatement.clearBatch ();
}
}
long l1 = System.currentTimeMillis ();
System.out.println ("使用时间:" + (l1 - l));
} catch (Exception e) {
e.printStackTrace ();
}finally {
JDBCUtils.close (resultSet , preparedStatement , coonect);
}
}
结果如下:
肥肠好用
其中使用了
- Statement.addBatch() 添加数据
- Statement.executeBatch() 处理数据
- Statement.clearBatch() 清空数据
addBatch中有一个Object类型的动态数组初始大小为10 一次扩容1.5(Arraylist)