JDBC编程学习
JDBC
JDBC简介
DBC（Java DataBase Connectivity）是Java和数据库之间的一个桥梁，是一个规范而不是一个实现，能够执行SQL语句。它由一组用Java语言编写的类和接口组成。
JDBC编程步骤
1）装载相应数据的JDBC驱动并初始化
1.下载mysql-connector.jar包
https://dev.mysql.com/downloads/connector/j/5.1.html
解压后就能在文件夹中找到mysql-connector-java-5.1.48-bin.jar包
2.IDEA导入jar包
File -> Project Structure -> Modules -> 添加mysql-connector-java-5.1.48-bin.jar包
3.初始化驱动
通过初始化驱动类com.mysql.jdbc.Driver，该类就在 mysql-connector-java-5.1.48-bin.jar中。
public class DbUtil {
public static void main(String[] args) {
try{
Class.forName("com.mysql.jdbc.Driver");
} catch(ClassNotFoundException e){
e.printStackTrace();
}
}
}
(Class.forName需要捕获ClassNotFoundException)
Class.forName是把这个类加载到JVM中，加载的时候，就会执行其中的静态初始化块，完成驱动的初始化的相关工作。
2）建立JDBC和数据库之间的connection连接
创建连接需要数据库的url、user、password等信息。
我们在DbUtil类中创建以一个工具方法getConnection（），同时创建close（）方法用于释放资源。
package com.jdbctest.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DbUtil {
public static final String URL = "jdbc:mysql://localhost:3306/timeline?characterEncoding=UTF-8";
public static final String USER = "root";
public static final String PASSWORD ="123456";
/**
* 获取数据库连接
* @return 一个数据库连接
*/
public static Connection getConnection(){
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(URL,USER,PASSWORD);
} catch (ClassNotFoundException e){
e.printStackTrace();
} catch (SQLException e){
e.printStackTrace();
}
return conn;
}
/**
* 释放资源
*/
public static void close(ResultSet rs){
if(rs != null)
try {
rs.close();
} catch (SQLException e){
e.printStackTrace();
}
}
public static void close(PreparedStatement pstmt){
if(pstmt != null)
try {
pstmt.close();
} catch (SQLException e){
e.printStackTrace();
}
}
public static void close(Connection conn){
if(conn != null)
try {
conn.close();
} catch (SQLException e){
e.printStackTrace();
}
}
url中需要根据自己选择的数据库更改参数：
3）创建preparedstatement执行SQL语句
增删改的代码基本类似，查询稍微复杂一点。
增删改
- 写好sql语句（“?”是占位符）
- 建立连接
- 创建preparedstatement并为set方法给占位符赋值
- 执行语句
- 释放资源（关闭接口和连接等）
查询
- 创建用于储存集合元素的list
- 建立连接、创建preparestatement对象执行sql
- 将sql执行结果赋值给resultset对象
- 依次取出结果并创建元素对象，并将元素对象加入列表
- 返回列表
- 释放资源
实例
1.我们根据数据库中user表，创建实体类User类
package com.jdbctest.utity;
public class User {
private int id;
private String username;
private String password;
public User(int id, String username, String password){
this.id = id;
this.username = username;
this.password = password;
}
public void setId(int id){
this.id = id;
}
public int getId(){
return id;
}
public void setUsername(String username) {
this.username = username;
}
public String getUsername() {
return username;
}
public void setPassword(String password) {
this.password = password;
}
public String getPassword() {
return password;
}
}
2.新建dao层，创建UserDao接口
package com.jdbctest.dao;
import com.jdbctest.utity.User;
import java.util.List;
public interface UserDao {
/**
*
* @param user
* @return int
*/
public int insertUser(User user);
/**
*
* @param id
* @return int
*/
public int deleteUser(int id);
/**
*
* @param user
* @return int
*/
public int updateUser(User user);
/**
*
* @param index
* @param pageSize
* @return userlist
*/
public List<User> listUserByPage(int index, int pageSize);
/**
*
* @param username
* @param pwd
* @return user
*/
public User findUserByNamePwd(String username, String pwd);
/**
*
* @param id
* @return user
*/
public User findUserById(int id);
}
3.新建DaoImp层，创建UserDaoImp类，实现UserDao接口方法
package com.jdbctest.daoimp;
import com.jdbctest.dao.UserDao;
import com.jdbctest.util.JDBCUtil;
import com.jdbctest.utity.User;
import jdk.nashorn.internal.scripts.JD;
import javax.sql.rowset.JdbcRowSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class UserDaoImp implements UserDao {
@Override
public int insertUser(User user) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
String sql = "insert into user values(?,?,?)";
//建立连接
conn = JDBCUtil.getConnnection();
pstmt = conn.prepareStatement(sql);
//利用set方法给占位符赋值
pstmt.setInt(1,user.getId());
pstmt.setString(2,user.getUsername());
pstmt.setString(3,user.getPassword());
//执行sql
int num = pstmt.executeUpdate();
return num;
} catch (SQLException e){
e.printStackTrace();
} finally {
JDBCUtil.close(pstmt);
JDBCUtil.close(conn);
}
return 0;
}
@Override
public int deleteUser(int id) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
String sql = "delete from user where id=?";
conn = JDBCUtil.getConnnection();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,id);
int num = pstmt.executeUpdate();
return num;
} catch (SQLException e){
e.printStackTrace();
} finally {
JDBCUtil.close(pstmt);
JDBCUtil.close(conn);
}
return 0;
}
@Override
public int updateUser(User user) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
String sql = "update user set username=?,password=? where id=?";
conn = JDBCUtil.getConnnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,user.getUsername());
pstmt.setString(2,user.getPassword());
pstmt.setInt(3,user.getId());
int num = pstmt.executeUpdate();
return num;
} catch (SQLException e){
e.printStackTrace();
} finally {
JDBCUtil.close(pstmt);
JDBCUtil.close(conn);
}
return 0;
}
@Override
public User findUserById(int id) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
User user = null;
try {
String sql = "select * from user where id=?";
conn = JDBCUtil.getConnnection();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,id);
rs = pstmt.executeQuery();
while (rs.next()){
user = new User(rs.getInt("id"),rs.getString("username"),rs.getString("password"));
}
return user;
} catch (SQLException e){
e.printStackTrace();
} finally {
JDBCUtil.close(rs);
JDBCUtil.close(pstmt);
JDBCUtil.close(conn);
}
return null;
}
@Override
public User findUserByNamePwd(String username, String pwd) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
User user = null;
try {
String sql = "select * from user where username=? and password=?";
conn = JDBCUtil.getConnnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,username);
pstmt.setString(2,pwd);
rs = pstmt.executeQuery();
while(rs.next()){
user = new User(rs.getInt(1),rs.getString(2,rs.getString(3));
}
return user;
} catch (SQLException e){
e.printStackTrace();
} finally {
JDBCUtil.close(rs);
JDBCUtil.close(pstmt);
JDBCUtil.close(conn);
}
return null;
}
@Override
public List<User> listUserByPage(int index, int pageSize) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
User user = null;
//用于储存user的list
List<User> userList = new ArrayList<User>();
try {
String sql = "select * from user limit ?,?";
//建立连接
conn = JDBCUtil.getConnnection();
pstmt = conn.prepareStatement(sql);
//给占位符赋值
pstmt.setInt(1,pageSize*(index-1));
pstmt.setInt(2,pageSize);
//执行sql
rs = pstmt.executeQuery();
//遍历rs，取出数据生成user对象，并依次加入userList
while(rs.next()){
user = new User(rs.getInt("id"),rs.getString("username"),rs.getString("password"));
userList.add(user);
}
return userList;
} catch (SQLException e){
e.printStackTrace();
} finally {
JDBCUtil.close(rs);
JDBCUtil.close(pstmt);
JDBCUtil.close(conn);
}
return null;
}
}
sql语句中的“？”是占位符，赋值时占位符的索引是从1开始的。
ResultSet.getXX()方法的括号里面可以填属性值，还可以填该属性在数据表中的列号，从1开始编码。
例如
findUserByNamePwd()中
user = new User(rs.getInt(1),rs.getString(2),rs.getString(3));
和findUserById()中
user = new User(rs.getInt("id"),rs.getString("username"),rs.getString("password"));
两个语句是等价的。
4.简单测试功能
package com.jdbctest;
import com.jdbctest.daoimp.UserDaoImp;
import com.jdbctest.utity.User;
public class Main {
public static void main(String[] args) {
User user = new User(1,"tom","123");
UserDaoImp udl = new UserDaoImp();
//测试插入
int num = udl.insertUser(user);
if(num != 0)
System.out.println("数据插入成功");
else
System.out.println("数据插入失败");
//测试查询
System.out.println(udl.findUserById(1).toString());
//测试更新
User user1 = new User(1,"tom","123456");
udl.updateUser(user1);
System.out.println(udl.findUserById(1).toString());
//测试删除
num = udl.deleteUser(1);
if(num != 0)
System.out.println("数据删除成功");
else
System.out.println("数据删除失败");
}
}
最后项目结构为：
参考
https://blog.csdn.net/jungle_rao/article/details/81274720
https://blog.csdn.net/doctor_ly/article/details/81240720
123
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
