JDBC
准备jar包
Java连接MySQL数据库需要用到mysql-connector-java-5.1.12-bin.jar。将其放到加到项目依赖即可
连接 MySQL的步骤
1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
2.连接数据库
Connection connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/ssm","root","123456");
驱动:com.mysql.jdbc.Driver
URL:jdbc:mysql://machine_name:port/dbname
注:machine_name:数据库所在的机器的名称;
port:端口号,默认3306
3.执行查询(select)、更新(update,insert,delete)获得结果集
//以查询为例
PreparedStatement preparedStatement = connection.prepareStatement("select * from t_user");
//遍历结果集
while (resultSet.next()){
System.out.println(resultSet.getInt(1) + "===="
+ resultSet.getString(2) + "====" + resultSet.getInt(3));
}
遍历结果集建议使用while,无论结果是只有一个还是集合
4.关闭相关资源
resultSet.close();
preparedStatement.close();
connection.close();
完整代码
package com.baidu.jdbc;
import java.sql.*;
public class jdbcdemo1 {
public static void main(String[] args) {
try {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//连接数据库
Connection connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/ssm","root","123456");
// 执行sql语句
PreparedStatement preparedStatement = connection.prepareStatement("select * from t_user");
//获得结果集
ResultSet resultSet = preparedStatement.executeQuery();
//遍历结果集
while (resultSet.next()){
System.out.println(resultSet.getInt(1) + "===="
+ resultSet.getString(2) + "====" + resultSet.getInt(3));
}
//关闭资源
resultSet.close();
preparedStatement.close();
connection.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
resultSet.getInt(1)参数是数据库表中数据的列数(第几列)
getInt(),getString()对应的是该列的数据类型
代码优化
把固定的代码放到变量中
String diver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/ssm";
String user="root";
String pwd="123456";
String sql="";
Connection connection=null;
PreparedStatement preparedStatement=null;
把关闭资源的代码放到finally中
finally {
//关闭资源
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
占位符设置参数
String sql="update t_user set name=? where id=?";
//第一个参数是?在sql中的位置,第二个参数是要设置的值
preparedStatement.setString(1,"hahaha");
preparedStatement.setInt(2,111);
在使用占位符设置参数要注意数据类型一致
在实际开发中,上述第二个参数通常是Java变量
封装JDBC工具类
废话不多说,直接上代码
import java.sql.*;
public class JdbcUtils {
public static String driver="com.mysql.jdbc.Driver";
public static String url="jdbc:mysql://localhost:3306/ssm?useUnicode=true&characterEncoding=utf-8";
public static String username="root";
public static String pwd="123456";
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConn(){
Connection connection=null;
try {
connection=DriverManager.getConnection(url, username, pwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static void close(ResultSet rs, PreparedStatement pst,Connection connection){
if(null != rs){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != pst){
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != connection){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}