MySQL的JDBC编程及增删改查

    ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/lmp?characterEncoding=utf8&useSSL=false");
    //设置登录数据库的用户名
    ((MysqlDataSource)dataSource).setUser("root");
    //设置登录数据库的密码
    ((MysqlDataSource)dataSource).setPassword("woshizhu123");

2. 通过Connection连接数据库(输入密码连接成功)

//import java.sql.Connection; Connection connection = dataSource.getConnection();

3. 拼接sql语句(写入sql语句)

String sql = "insert into student values(1,'张三')";

4. 将sql语句包装成对象

PreparedStatement statement = connection.prepareStatement(sql);

5. 执行sql语句(按下回车执行sql语句)

int ret = statement.executeUpdate();

* 执行 update delete insert 使用 executeUpdate() 方法
* 执行 select 使用 executeQuery() 方法
* 使用 executeQuery() 方法 会返回一个resultSet集合, 包含查找到的数据, 初始情况下resultSet不指向任一行记录, 使用next,让他指向第一条记录, 再使用next指向下一条记录


6. 释放资源

statement.close(); connection.close();

#### 利用JDBC实现增加(insert)

public class TestJDBC { 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/java102?characterEncoding=utf-8&useSSL=false"); ((MysqlDataSource)dataSource).setUser("root"); ((MysqlDataSource)dataSource).setPassword("woshizhu123"); Connection connection = dataSource.getConnection(); System.out.println("输入id"); int id = scanner.nextInt(); System.out.println("输入名字"); String name = scanner.next(); String sql = "insert into student values(?,?)"; PreparedStatement statement = connection.prepareStatement(sql); statement.setInt(1,id); statement.setString(2,name); int ret = statement.executeUpdate(); if(ret == 1){ System.out.println("插入成功"); }else { System.out.println("插入失败"); } statement.close(); connection.close();

}

}

### 利用JDBC实现删除(delete)

public class TestJDBCDelete { public static void main(String[] args) throws SQLException { DataSource dataSource = new MysqlDataSource(); ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java102?characterEncoding=utf8&useSSL=false"); ((MysqlDataSource)dataSource).setUser("root"); ((MysqlDataSource)dataSource).setPassword("woshizhu123"); Connection connection = dataSource.getConnection(); Scanner scanner = new Scanner(System.in); System.out.println("请输入要删除的id"); int id = scanner.nextInt(); String sql = "delete from student where id = ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1,id); int ret = preparedStatement.executeUpdate(); System.out.println(ret); preparedStatement.close(); connection.close(); }

### 利用JDBC实现修改(update)

public class TestJDBCUpdate { public static void main(String[] args) throws SQLException { DataSource dataSource = new MysqlDataSource(); ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java102?characterEncoding=utf8&useSSL=false"); ((MysqlDataSource)dataSource).setUser("root"); ((MysqlDataSource)dataSource).setPassword("woshizhu123"); Connection connection = dataSource.getConnection(); Scanner scanner = new Scanner(System.in); System.out.println("请输入要修改的学生id"); int id = scanner.nextInt(); System.out.println("请输入要修改的学生姓名"); String name = scanner.next(); String sql = "update student set name = ? where id = ?"; PreparedStatement statement = connection.prepareStatement(sql); statement.setString(1,name); statement.setInt(2,id); int ret = statement.executeUpdate(); System.out.println(ret); statement.close(); connection.close(); } }

### 利用JDBC实现查找(select)

public static void testJDBCSelect() throws SQLException { //1创建DataSource对象 DataSource dataSource = new MysqlDataSource(); //2连接数据库 ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java_5_31?characterEncoding=utf-8&useSSL=true"); ((MysqlDataSource)dataSource).setUser("root"); ((MysqlDataSource)dataSource).setPassword("listen"); Connection connection = dataSource.getConnection(); //3拼接sql String sql = "select * from student"; PreparedStatement statement = connection.prepareStatement(sql); //4执行sql ResultSet resultSet = statement.executeQuery(); //5遍历得到的集合 while (resultSet.next()) { int id = resultSet.getInt("id"); String name = resultSet.getString("name"); int classId = resultSet.getInt("classId"); System.out.println("id " + id + " name " + name + " classId " + classId); } //6关闭资源 resultSet.close(); statement.close(); connection.close(); }

****先赞后看，养成习惯！！！^ \_ ^♥♥♥**  
 每天都更新知识点哦！！！  
 码字不易，大家的支持就是我坚持下去的动力。点赞后不要忘记关注我哦！**