初学者的java学习笔记——JDBC的使用练习
这里是一只小萌新 [1]
程序描述
本文是练习程序，旨在自我回顾以及巩固。
本文主要内容是JDBC连接并操作数据库的设计与实现，没有啥高大尚的内容，就是这么的朴实无华，若有缺点，还望各路高手指正。
因为只涉及基本的CURD操作，所以本程序未添加任何事务操作。
先来一句点兔万岁！
使用工具
idea
mysql
navicat for mysql
程序结构
代码
首先是创建存储数据的类：
package domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Rabbit {
private Long id;
private String name;
private Integer age;
private String store;
private String voice;
}
接着是DAO接口：
package dao;
import domain.Rabbit;
import java.util.List;
public interface IRabbitDAO {
/**
* 用于增加新的数据
* @param xb 要插入的数据
*/
void insert(Rabbit xb);
/**
* 用于删除指定的数据
* @param id 要删除的数据的id
*/
void delete(Long id);
/**
* 用于修改指定的数据
* @param xb 要用于修改的数据
*/
void update(Rabbit xb);
/**
* 查询单条数据
* @param id 要查询的数据的id
* @return 返回相应的数据的对象
*/
Rabbit selectOne(Long id);
/**
* 查询整个表的数据
* @return 返回包含了整个表数据对象的集合
*/
List<Rabbit> selectAll();
}
然后是用于获取连接对象以及用于关闭的工具类（本文使用的连接池是阿里的Druid）：
package util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtil {
private static DataSource dataSource = null;
/**
* 用于初始化从连接池获取dataSource对象
*/
static {
Properties p = new Properties();
InputStream resourceAsStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
try {
p.load(resourceAsStream);
dataSource = DruidDataSourceFactory.createDataSource(p);
}catch (Exception e) {
e.printStackTrace();
}
}
/**
* 通过上方获取的DataSource的对象获取连接对象
* @return 返回连接对象
*/
public static Connection getConnection() {
Connection conn = null;
try {
conn = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 用于关闭各种对象
* @param connection 连接对象
* @param statement 语句对象
*/
public static void close(Connection connection,Statement statement) {
try {
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 方法重载
* @param connection 连接对象
* @param statement 语句对象
* @param resultSet 结果集对象
*/
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
try {
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
最后是万众瞩目的实现类：
package dao.impl;
import dao.IRabbitDAO;
import domain.Rabbit;
import util.JDBCUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class RabbitDAOImpl implements IRabbitDAO {
@Override
public void insert(Rabbit xb) {
String sql = "INSERT INTO rabbit_order(name,age,store,voice) VALUES(?,?,?,?)";
Connection conn = JDBCUtil.getConnection();
PreparedStatement pst = null;
try {
pst = conn.prepareStatement(sql);
pst.setString(1,xb.getName());
pst.setInt(2,xb.getAge());
pst.setString(3,xb.getStore());
pst.setString(4,xb.getVoice());
pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.close(conn,pst);
}
}
@Override
public void delete(Long id) {
String sql = "DELETE FROM rabbit_order WHERE id=?";
Connection conn = JDBCUtil.getConnection();
PreparedStatement pst = null;
try {
pst = conn.prepareStatement(sql);
pst.setLong(1,id);
pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.close(conn,pst);
}
}
@Override
public void update(Rabbit xb) {
String sql = "UPDATE rabbit_order SET name=?,age=?,store=?,voice=? WHERE id=?";
Connection conn = JDBCUtil.getConnection();
PreparedStatement pst = null;
try {
pst = conn.prepareStatement(sql);
pst.setString(1,xb.getName());
pst.setInt(2,xb.getAge());
pst.setString(3,xb.getStore());
pst.setString(4,xb.getVoice());
pst.setLong(5,xb.getId());
pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.close(conn,pst);
}
}
@Override
public Rabbit selectOne(Long id) {
String sql = "SELECT * FROM rabbit_order WHERE id=?";
Connection conn = JDBCUtil.getConnection();
PreparedStatement pst = null;
ResultSet resultSet = null;
try {
pst = conn.prepareStatement(sql);
pst.setLong(1,id);
resultSet = pst.executeQuery();
if(resultSet.next()) {
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String store = resultSet.getString("store");
String voice = resultSet.getString("voice");
return new Rabbit(id,name,age,store,voice);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.close(conn,pst,resultSet);
}
return null;
}
@Override
public List<Rabbit> selectAll() {
String sql = "SELECT * FROM rabbit_order";
Connection conn = JDBCUtil.getConnection();
PreparedStatement pst = null;
ResultSet resultSet = null;
List<Rabbit> list = new ArrayList<>();
try {
pst = conn.prepareStatement(sql);
resultSet = pst.executeQuery();
while(resultSet.next()) {
long id = resultSet.getLong("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String store = resultSet.getString("store");
String voice = resultSet.getString("voice");
list.add(new Rabbit(id,name,age,store,voice));
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.close(conn,pst,resultSet);
}
return null;
}
}
对了对了，差点忘了，建表语句奉上：
CREATE TABLE rabbit_order (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
age INT,
store VARCHAR(25),
voice VARCHAR(10)
);
INSERT INTO rabbit_order(name,age,store,voice) VALUES('保登心爱',15,'Rabbit House','佐仓绫音');
INSERT INTO rabbit_order(name,age,store,voice) VALUES('香风智乃',13,'Rabbit House','水濑祈');
INSERT INTO rabbit_order(name,age,store,voice) VALUES('天天座理世',16,'Rabbit House','种田梨沙');
INSERT INTO rabbit_order(name,age,store,voice) VALUES('宇治松千夜',15,'甘兔庵','佐藤聪美');
INSERT INTO rabbit_order(name,age,store,voice) VALUES('桐间纱路',15,'Fleur du Lapin','内田真礼');
INSERT INTO rabbit_order(name,age,store,voice) VALUES('条河麻耶',15,null,'徳井青空');
INSERT INTO rabbit_order(name,age,store,voice) VALUES('奈津惠',15,null,'村川梨衣');
总结（个人学习笔记奉上）
我是可爱的萌新，请对我好一点。 ↩︎
浙公网安备 33010602011771号