什么是JDBC : Java连接数据库!
需要jar包的支持:
java.sql
javax.sql
mysql-conneter-java… 连接驱动(必须要导入)
实验环境搭建
CREATE TABLE users( id INT PRIMARY KEY, `name` VARCHAR(40), `password` VARCHAR(40), email VARCHAR(60), birthday DATE ); INSERT INTO users (id,`name`,`password`,email,birthday) VALUES(1,'张三','123456','123456@qq.com','2000-01-01'); INSERT INTO users (id,`name`,`password`,email,birthday) VALUES(2,'李四','123456','zhangsan@qq.com','2000-01-02'); INSERT INTO users (id,`name`,`password`,email,birthday) VALUES(3,'王五','123456','wangwu@qq.com','2000-01-03'); select* from users;
导入数据库依赖
<!--mysql的驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency>
IDEA中连接数据库:
JDBC 固定步骤:
加载驱动
连接数据库,代表数据库
向数据库发送SQL的对象Statement : CRUD
编写SQL (根据业务,不同的SQL)
执行SQL
关闭连接(先开的后关)
Testjdbc.java
select
public class Testjdbc { public static void main(String[] args) throws ClassNotFoundException, SQLException { //配置信息 //useUnicode=true&characterEncoding=utf-8 解决中文乱码 String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8"; String username = "root"; String password = "111111"; //1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); //2.连接数据库,代表数据库 Connection connection = DriverManager.getConnection(url, username, password); //3.向数据库发送SQL的对象Statement,PreparedStatement : CRUD Statement statement = connection.createStatement(); //4.编写SQL String sql = "select * from users"; //5.执行查询SQL,返回一个 ResultSet : 结果集 ResultSet rs = statement.executeQuery(sql); while (rs.next()){ System.out.println("id="+rs.getObject("id")); System.out.println("name="+rs.getObject("name")); System.out.println("password="+rs.getObject("password")); System.out.println("email="+rs.getObject("email")); System.out.println("birthday="+rs.getObject("birthday")); } //6.关闭连接,释放资源(一定要做) 先开后关 rs.close(); statement.close(); connection.close(); } }
delete
//delete //String sql = "delete from users where id = 4"; //delete 受影响的行数 ,增删该都是用executeUpdate 即可 //int i = statement.executeUpdate( sql );
预编译SQL
preparedStatement
spublic class Testjdbc2 { public static void main(String[] args) throws ClassNotFoundException, SQLException { //配置信息 //useUnicode=true&characterEncoding=utf-8 解决中文乱码 String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8"; String username = "root"; String password = "111111"; //1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); //2.连接数据库,代表数据库 Connection connection = DriverManager.getConnection(url, username, password); //3.编写SQL String sql = "insert into users(id, name, password, email, birthday) values (?,?,?,?,?);"; //4.预编译 PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1,4);//给第一个占位符? 的值赋值为4; preparedStatement.setString(2,"狂神说Java");//给第二个占位符? 的值赋值为狂神说Java; preparedStatement.setString(3,"123456");//给第三个占位符? 的值赋值为123456; preparedStatement.setString(4,"24736743@qq.com");//给第四个占位符? 的值赋值为1; preparedStatement.setDate(5,new Date(new java.util.Date().getTime()));//给第五个占位符? 的值赋值为new Date(new java.util.Date().getTime()); //5.执行SQL int i = preparedStatement.executeUpdate(); if (i>0){ System.out.println("插入成功@"); } //6.关闭连接,释放资源(一定要做) 先开后关 preparedStatement.close(); connection.close(); } }