JDBC
- JDBC:Java DataBase Connectivity(Java数据库链接)可以理解为一个协议Java程序访问数据库通道之一。
- Java连接数据库步骤
1、注册驱动(打开Java程序与数据库之间的通道,驱动包又数据库厂商提供)
/*常用的两种初始化驱动方式
1、DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
2、Class.forName("com.mysql.cj.jdbc.Driver"); //通过类加载的方式将驱动放在内存静态区提前加载
*/
2、建立数据库链接
String url = "jdbc:mysql://localhost:3306/test"; //数据库地址
String name = "root";//数据库用户名
String password = "root";//数据库用户密码
Connection conn = DriverManager.getConnection(url,name,password);
3、获取数据操作对象
4、执行SQL语句
/*
两个对象可以实现Statement 和 PreparedStatement
1、Statement statement = conn.createStatement()
String sql = "select * from user";
statement.executeQuery(sql)
2、(推荐)PreparedStatement先编译后通过占位符传值,解决SQL注入问题;只编译一次,提高效率
String sql = "select id as userid,name from user where id=? and name=?";
PreparedStatement ps = conn.prapareStatement(sql)
ps.setInt(1,1);
ps.String(2,"Tom");
ps.executeQuery();
*/
5、处理查询结果集(如果第4步是查询语句才会又这一步)
/*
executeQuery(sql)返回的是一个ResultSet结果集
ResultSet rs = ps.executeQuery();
if(rs.next()){
String id = getString("userid");//取数据,每次操作一行,一个实例getString() 不管数据库中的数据类型是什么,都以String的形式取出
//JDBC中所有下标从1开始。String id = getString(1);
String name = getString("name");//String name = getString(2);
System.out.println(id+"-"+name);
}
*/
6、释放资源
为了避免资源占用浪费,执行完后将资源关闭 rs.close();
-
补充: 查询DQL时用executeQuery,DML增删改用executeUpdate 返回值时影响行数。
-
查询语句完整示例
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
String sql = "select id,username from user where id=? and username=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 2);
ps.setString(2, "Tom");
rs = ps.executeQuery();
if (rs.next()) {
System.out.println(rs.getString("id") + rs.getString("usernaem"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
- 插入示例
Connection conn = null;
PreparedStatement ps = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
String sql="insert into user values(?,?)";
ps=conn.prepareStatement(sql);
ps.setInt(1,4);
ps.setString(2,"Jerry");
System.out.println(ps.executeUpdate());
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
- 更新示例
Connection conn = null;
PreparedStatement ps = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
String sql = "update user set id=?,username=? where id=?";
ps=conn.prepareStatement(sql);
ps.setInt(1,5);
ps.setString(2,"jerry");
ps.setInt(3,4);
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
- 删除示例
Connection conn = null;
PreparedStatement ps = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
String sql="delete from user where id=?";
ps=conn.prepareStatement(sql);
ps.setInt(1,5);
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}