文章目录
1.建立数据库连接
方式一:通过DriverManager(驱动管理类)的静态方法获取
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//配置连接字符串
String url = "jdbc:mysql://127.0.0.1:3306/test?user=root&" +
"password=123456&useUnicode=true&characterEncoding=UTF-8";
//创建数据库连接对象
try {
Connection connection = DriverManager.getConnection(url);
} catch (SQLException e) {
e.printStackTrace();
}
方式二:通过Datasource(数据源)对象获取
private static String URL = "jdbc:mysql://127.0.0.1:3306/github_crawler?characterEncoding=utf-8&useSSL=true";
private static String USERNAME = "root";
private static String PASSWORD = "123456";
private static volatile DataSource dataSource = null;
private static DataSource getDataSource() {
//双重if判定
if (dataSource == null) {
//加锁,保证线程安全
synchronized (DBUtil.class) {
if (dataSource == null) {
dataSource = new MysqlDataSource();
//向下转型
MysqlDataSource mysqlDataSource = (MysqlDataSource) dataSource;
mysqlDataSource.setURL(URL);
mysqlDataSource.setUser(USERNAME);
mysqlDataSource.setPassword(PASSWORD);
}
}
}
return dataSource;
}
public static Connection getConnection() {
try {
return getDataSource().getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
两种方式的区别
1.DriverManager类获取的Connection连接,是无法重复利用的,每当使用完释放资源时,通过Connection.close()都是关闭物理连接。
2.DataSource提供连接池的支持,连接池在初始化时将创建一定数量的可复用的数据库连接,每当使用完释放资源时,Connection.close()都是将Connection连接对象回收。
2.创建操作命令及执行SQL语句
方式一:Statement对象
String sql = "select * from table"; //不带参数的SQL语句
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
方式二:PreparedStatement对象
查询
String sql = "select * from table where name=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "harry");
ResultSet resultSet = preparedStatement.executeQuery(); //返回单个结果集
增删改
String sql = "insert into table values (?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "harry"); //占位符下标从1开始
preparedStatement.setString(2, "man");
preparedStatement.setInt(3, 18);
int ret = preparedStatement.executeUpdate(); //返回受影响的行数
两种方式的区别
1.Statement对象用于执行不带参数的简单SQL语句
2.PreparedStatement对象
- 用于执行带或不带参数的SQL语句
- SQL语句会预编译在数据库系统
- 执行速度快于Statement对象
3.处理结果集
查询
while (resultSet.next()) {
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
int age = resultSet.getInt("age");
}
增删改
if (ret != 1) {
System.out.println("数据库执行出错");
} else {
System.out.println("数据库插入成功");
}
4.释放资源
public static void close(Connection connection, PreparedStatement preparedStatement,
ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}