一、jdbc概念介绍
jdbc,即java database connectivity,是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准,据此可以构建更高级的工具和接口,使数据库开发人员能够编写数据库应用程序.我理解为就是使用java语言访问数据库数据的一套java语言接口,方法等。
二、准备工作
1.首先需要java连接数据库的jar包:mysql-connector-java-5.1.44-bin.jar。
2.然后dos命令窗口连接数据库,创建一张user用户表(mysql命令符使用可以查看这 篇 http://blog.csdn.net/qq_36551763/article/details/78455442),我创建的表如下:
三、撸代码
1.先写一个对应的user类,代码如下:
public class User {
private int id;
private String username;
private String password;
private String sex;
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
2.创建一个数据库操作的工具类,核心内容
public class DBHelper {
public static final String url = "jdbc:mysql://localhost:3306/testdb1";//数据库地址,本篇采用自己主机电脑创建的数据库
public static final String name = "com.mysql.jdbc.Driver";//固定写法
public static final String users = "root";//数据库使用者的用户名
public static final String password = "hu19910127";//你访问数据库的密码
public Connection conn = null;
public PreparedStatement pst = null;
public static Connection getConnection(){
Connection conn = null;
try {
Class.forName(name);
conn = DriverManager.getConnection(url, users, password);//注册驱动,关键步骤
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 插入记录
* @param user
* @return
*/
public static int insert(User user){
Connection conn = getConnection();
String sql = "insert into user values(?, ?, ?,?,?)";//?是占位符,后面psmt通过位置给其设置参数
PreparedStatement psmt = null;
try {
psmt = conn.prepareStatement(sql);
psmt.setInt(1, user.getId());
psmt.setString(2, user.getUsername());
psmt.setString(3, user.getPassword());
psmt.setString(4, user.getSex());
psmt.setString(5, user.getAddress());
int count = psmt.executeUpdate();
return count;
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(psmt != null){
try {
psmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return -1;
}
/**
*通过id删除某一条记录
* @param id
* @return
*/
public static int delete(Integer id){
Connection con = getConnection();
String sql = "delete from user where id=?";
PreparedStatement psmt = null;
try {
psmt = con.prepareStatement(sql);
psmt.setInt(1, id);
int count = psmt.executeUpdate();
return count;
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(psmt != null){
try {
psmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return -1;
}
/**
* 通过id,修改对应的记录的信息
* @param user
*/
public static void update(User user){
Connection con = getConnection();
String sql = "update user set username = ?,password = ?,sex = ?,address = ? where id = ?";
PreparedStatement psmt = null;
try {
psmt = con.prepareStatement(sql);
psmt.setString(1, user.getUsername());
psmt.setString(2, user.getPassword());
psmt.setString(3, user.getSex());
psmt.setString(4, user.getAddress());
psmt.setInt(5, user.getId());
psmt.execute();
} catch (Exception e) {
e.printStackTrace();
}finally{
if(psmt != null){
try {
psmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 通过id查找单个user
* @param id
* @return
*/
public static User queryById(Integer id){
Connection con = getConnection();
String sql = "select * from user where id = ?";
PreparedStatement psmt = null;
ResultSet resultSet = null;
User user=null;
try {
psmt = con.prepareStatement(sql);
psmt.setInt(1, id);
resultSet = psmt.executeQuery();
while (resultSet.next()){
user = new User();
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setSex(resultSet.getString("sex"));
user.setAddress(resultSet.getString("address"));
}
return user;
} catch (Exception e) {
e.printStackTrace();
}finally{
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(psmt != null){
try {
psmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return null;
}
/**
* 通过username查找所有符合条件的记录
* @param username
* @return
*/
public static User queryByUsername(String username){
Connection con = getConnection();
String sql = "select * from user where username = ?";
PreparedStatement psmt = null;
User user=null;
try {
psmt = con.prepareStatement(sql);
psmt.setString(1, username);
ResultSet resultSet = psmt.executeQuery();
while (resultSet.next()){
user = new User();
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setSex(resultSet.getString("sex"));
user.setAddress(resultSet.getString("address"));
}
return user;
} catch (Exception e) {
e.printStackTrace();
}finally{
if(psmt != null){
try {
psmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return null;
}
/**
* 通过address查找所有符合条件的记录
* @param address
* @return
*/
public static List<User> queryByAddress(String address){
List<User> list = new ArrayList<>();
Connection con = getConnection();
String sql = "select * from user where address = ?";
PreparedStatement psmt = null;
User user=null;
try {
psmt = con.prepareStatement(sql);
psmt.setString(1, address);
ResultSet resultSet = psmt.executeQuery();
while (resultSet.next()){
user = new User();
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setSex(resultSet.getString("sex"));
user.setAddress(resultSet.getString("address"));
list.add(user);
}
return list;
} catch (Exception e) {
e.printStackTrace();
}finally{
if(psmt != null){
try {
psmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return null;
}
/**
* 查询所有的记录,以集合的形式返回
* @return
*/
public static List<User> queryAllUser(){
List<User> userList = new ArrayList<>();
Connection con = getConnection();
String sql = "select * from user";
PreparedStatement psmt = null;
User user = null;
try {
psmt = con.prepareStatement(sql);
ResultSet resultSet = psmt.executeQuery();
while (resultSet.next()){
user = new User();
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setSex(resultSet.getString("sex"));
user.setAddress(resultSet.getString("address"));
userList.add(user);
}
return userList;
} catch (Exception e) {
e.printStackTrace();
}finally{
if(psmt != null){
try {
psmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return null;
}
}
结语:最原声的jdbc开发,核心内容就是那一步注册驱动,后面使用preparedstatement执行sql语句对数据库进行实质性的增删改查操作。