我们可以利用官方为java编写的数据库驱动包,利用idea来操作MySQL数据库
概念
其全称是java database connectivity,也就是提供了一系列接口,使java程序和Mysql连接,让sql语句在java中执行
准备
我们需要去下面这个网址下载数据库驱动包
https://mvnrepository.com/artifact/mysql/mysql-connector-java
选择的版本号需要和我们的Mysql的版本号相同,例如8.0的就选择8系列的
然后将这个jar包拷贝,创建一个新的项目,和一个文件夹
然后右键点击项目名称,点击Open Module Settings
然后点击Dependencies
然后点击加号,导入我们的lib文件夹的jar包
这样我们就配置好我们的项目了,接下来就可以编写代码了
代码
通过一下代码,可以连接到我们的数据库
import com.mysql.cj.jdbc.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class test {
public static void main(String[] args) throws SQLException {
DataSource dataSource = new MysqlDataSource();
((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
((MysqlDataSource) dataSource).setUser("root");
((MysqlDataSource) dataSource).setPassword("1234");
Connection connection = dataSource.getConnection();
}
}
前面的代码只要使用的java项目和自己的MySQL在同一台机器上,并且没有改过MySQL的默认属性,就是一样的代码,唯一不一样的是password是我们MySQL的密码,替换即可
插入操作
public class TestJDBC {
public static void main(String[] args) throws SQLException {
DataSource dataSource = new MysqlDataSource();
((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
((MysqlDataSource) dataSource).setUser("root");
((MysqlDataSource) dataSource).setPassword("1234");
Connection connection = dataSource.getConnection();
String sql = "insert student values(1,'张三')";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
int row = preparedStatement.executeUpdate();
System.out.println(row);
preparedStatement.close();
connection.close();
}
}
需要注意的是,用完资源后,应该断开和数据库的连接
查询操作
public class TestJDBCSelect {
public static void main(String[] args) throws SQLException {
DataSource dataSource = new MysqlDataSource();
((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSl=false");
((MysqlDataSource) dataSource).setUser("root");
((MysqlDataSource) dataSource).setPassword("1234");
Connection connection = dataSource.getConnection();
String sql = "select * from student";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.println("id = " + id + " name = "+ name);
}
resultSet.close();
preparedStatement.close();
connection.close();
}
}
和插入操作不一样,更新操作是用这条语句来使操作生效
preparedStatement.executeQuery();
删除操作
public class TestJDBCDelete {
public static void main(String[] args) throws SQLException {
Scanner scanner = new Scanner(System.in);
DataSource dataSource = new MysqlDataSource();
((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
((MysqlDataSource) dataSource).setUser("root");
((MysqlDataSource) dataSource).setPassword("1234");
Connection connection = dataSource.getConnection();
System.out.println("input the id which you want to delete");
int id = scanner.nextInt();
String sql = "delete from student where id = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1,id);
int ret = statement.executeUpdate();
System.out.println("ret = " + ret);
statement.close();
connection.close();
}
}