大家初学java连接数据库一定遇到不少问题吧，我遇到这些问题也很头痛，所以我我把我初学jdbc连接数据库写的代码直接分享给大家。最后有完整代码可以直接复制运行
建表语句
CREATE TABLE `course` (
`Name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`Mark` int NULL DEFAULT NULL,
`Id` bigint NULL DEFAULT NULL
)
我测试的是homework数据库下的表格course。这个可以在连接里面指定：
jdbc:mysql://localhost:3306/homework
管理数据库的软件我用的Navicat
连接数据库
//数据库连接工具类
class DBConnection {
// 获取数据库连接
public static Connection getConnection() {
Connection con =null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/homework", "root", "password");
if (con==null) {
System.out.println("数据库连接失败");
}
} catch (SQLException |ClassNotFoundException e)
{
e.printStackTrace();
}
return con;
}
// 关闭数据库资源
public static void close(ResultSet rs, PreparedStatement pstmt, Connection conn) throws Exception {
try {
if (rs !=null ){
rs.close();
}
if (pstmt!=null ){
pstmt.close();
}
if (conn !=null ){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}dao模式实现增删改查
interface ICourseDao {
public int insert(Course course) throws Exception;
public int delete(long i) throws Exception;
public int update(Course course) throws Exception;
//查询表中所有课程信息并以列表形式返回
public List<Course> select() throws Exception;
//实现课程信息的增加、修改和删除
public int update(String sql, Object[] params) throws Exception;
}
//课程 DAO实现类，负责数据库访问操作的具体实现
class CourseDaoImpl implements ICourseDao {
private static final String SQL_INSERT = "insert into course (name,mark,id) values(?,?,?)";
private static final String SQL_DELETE = "delete from course where id=?";
private static final String SQL_UPDATE = "update course set name=?,mark=? where id=?";
public int insert(Course course) throws Exception {
return update(SQL_INSERT, new Object[] {course.getName(),course.getMark(),course .getId()});
}
public int delete(long i) throws Exception {
return update(SQL_DELETE, new Object[] { i });
}
public int update(Course course) throws Exception {
return update(SQL_UPDATE, new Object[] { course.getName(), course.getMark(), course.getId() });
}
//查询表中所有课程信息并以列表形式返回
public List<Course> select() throws Exception {
List<Course> courseList = new ArrayList<>() ;
Course course =null ;
//你的实现代码
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null ;
conn =DBConnection .getConnection() ;
pstmt = conn.prepareStatement("select * from course") ;
rs=pstmt .executeQuery() ;
while ( rs.next() ){
String Name=rs.getString(1) ;
int Mark=rs.getInt(2);
long Id=rs.getLong(3) ;
course =new Course(Name ,Mark ,Id );
courseList .add(course );
}
DBConnection .close(rs ,pstmt ,conn ) ;
return courseList;
}
//实现课程信息的增加、修改和删除
public int update(String sql, Object[] params) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
// 获取方法
conn =DBConnection .getConnection() ;
// 准备预编译
pstmt = conn.prepareStatement(sql) ;
// 处理参数
for (int i =0;i <params .length ;i++){
pstmt .setObject(i+1,params[i]) ;
}
// 执行SQL语句
int flag=pstmt.executeUpdate() ;
DBConnection .close(pstmt ,conn);
return flag ;
}
}实体对象类
class Course {
//你的实现代码
private String Name;
private int Mark;
private long Id;
public Course() {
}
public Course(String name, int mark, long id) {
Name = name;
Mark = mark;
Id = id;
}
public String getName() {
return Name;
}
public void setName(String name) {
Name = name;
}
public int getMark() {
return Mark;
}
public void setMark(int mark) {
Mark = mark;
}
public long getId() {
return Id;
}
public void setId(int id) {
Id = id;
}
@Override
public String toString() {
return "Course{" +
"Name='" + Name + '\'' +
", Mark=" + Mark +
", Id=" + Id +
'}';
}
}
测试代码
//分别测试课程信息的查询、增加啊、修改和删除功能
public class Main {
public static void main(String[] args) {
//你的实现代码
System.out.println("---------------菜单--------------");
System.out.println("1.插入，2.删除，3.更新，4.查询全部信息，5.退出");
Scanner input=new Scanner(System .in ) ;
int m=input .nextInt() ;
CourseDaoImpl courseDao =new CourseDaoImpl() ;
try {
if(m==1){
Course course =scanner_() ;
System.out.println("已经插入"+courseDao.insert(course)+"条记录");
}else if (m==2)
{
Course course =scanner_() ;
System.out.println( "已经删除"+courseDao.delete(course.getId())+"条记录");
}else if (m==3){
Course course =scanner_() ;
System.out.println("已经更新"+courseDao.update(course)+"条记录");
}else if (m==4){
System.out.println("以下为查询到的信息");
List <Course > courseList = courseDao .select() ;
for (Course c:courseList ){
System.out.println(c);
}
}else if (m==5){
System .exit(0);
}
main1(args ) ;//为了循环调用main方法
} catch (Exception e) {
e.printStackTrace();
}
}
//为了循环调用main方法
static void main1(String []args){
main(args );
}
static Course scanner_(){
System.out.println("请你输入内容");
Scanner scanner =new Scanner(System .in ) ;
System.out.println("姓名");
String Name=scanner .next();
System.out.println("分数");
int Mark=scanner .nextInt() ;
System.out.println("Id");
long Id=scanner.nextLong() ;
Course course =new Course(Name ,Mark ,Id ) ;
return course ;
}
}全部代码
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
//数据库连接工具类
class DBConnection {
// 获取数据库连接
public static Connection getConnection() {
Connection con =null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/homework", "root", "password");
if (con==null) {
System.out.println("数据库连接失败");
}
} catch (SQLException |ClassNotFoundException e)
{
e.printStackTrace();
}
return con;
}
// 关闭数据库资源
public static void close(ResultSet rs, PreparedStatement pstmt, Connection conn) throws Exception {
try {
if (rs !=null ){
rs.close();
}
if (pstmt!=null ){
pstmt.close();
}
if (conn !=null ){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
// 关闭数据库资源
public static void close(PreparedStatement pstmt, Connection conn) throws Exception {
//你的实现代码
try {
if (pstmt!=null ){
pstmt.close();
}
if (conn !=null ){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
interface ICourseDao {
public int insert(Course course) throws Exception;
public int delete(long i) throws Exception;
public int update(Course course) throws Exception;
//查询表中所有课程信息并以列表形式返回
public List<Course> select() throws Exception;
//实现课程信息的增加、修改和删除
public int update(String sql, Object[] params) throws Exception;
}
//课程 DAO实现类，负责数据库访问操作的具体实现
class CourseDaoImpl implements ICourseDao {
private static final String SQL_INSERT = "insert into course (name,mark,id) values(?,?,?)";
private static final String SQL_DELETE = "delete from course where id=?";
private static final String SQL_UPDATE = "update course set name=?,mark=? where id=?";
public int insert(Course course) throws Exception {
return update(SQL_INSERT, new Object[] {course.getName(),course.getMark(),course .getId()});
}
public int delete(long i) throws Exception {
return update(SQL_DELETE, new Object[] { i });
}
public int update(Course course) throws Exception {
return update(SQL_UPDATE, new Object[] { course.getName(), course.getMark(), course.getId() });
}
//查询表中所有课程信息并以列表形式返回
public List<Course> select() throws Exception {
List<Course> courseList = new ArrayList<>() ;
Course course =null ;
//你的实现代码
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null ;
conn =DBConnection .getConnection() ;
pstmt = conn.prepareStatement("select * from course") ;
rs=pstmt .executeQuery() ;
while ( rs.next() ){
String Name=rs.getString(1) ;
int Mark=rs.getInt(2);
long Id=rs.getLong(3) ;
course =new Course(Name ,Mark ,Id );
courseList .add(course );
}
DBConnection .close(rs ,pstmt ,conn ) ;
return courseList;
}
//实现课程信息的增加、修改和删除
public int update(String sql, Object[] params) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
// 获取方法
conn =DBConnection .getConnection() ;
// 准备预编译
pstmt = conn.prepareStatement(sql) ;
// 处理参数
for (int i =0;i <params .length ;i++){
pstmt .setObject(i+1,params[i]) ;
}
// 执行SQL语句
int flag=pstmt.executeUpdate() ;
DBConnection .close(pstmt ,conn);
return flag ;
}
}
//分别测试课程信息的查询、增加啊、修改和删除功能
public class Main {
public static void main(String[] args) {
//你的实现代码
System.out.println("---------------菜单--------------");
System.out.println("1.插入，2.删除，3.更新，4.查询全部信息，5.退出");
Scanner input=new Scanner(System .in ) ;
int m=input .nextInt() ;
CourseDaoImpl courseDao =new CourseDaoImpl() ;
try {
if(m==1){
Course course =scanner_() ;
System.out.println("已经插入"+courseDao.insert(course)+"条记录");
}else if (m==2)
{
Course course =scanner_() ;
System.out.println( "已经删除"+courseDao.delete(course.getId())+"条记录");
}else if (m==3){
Course course =scanner_() ;
System.out.println("已经更新"+courseDao.update(course)+"条记录");
}else if (m==4){
System.out.println("以下为查询到的信息");
List <Course > courseList = courseDao .select() ;
for (Course c:courseList ){
System.out.println(c);
}
}else if (m==5){
System .exit(0);
}
main1(args ) ;//为了循环调用main方法
} catch (Exception e) {
e.printStackTrace();
}
}
//为了循环调用main方法
static void main1(String []args){
main(args );
}
static Course scanner_(){
System.out.println("请你输入内容");
Scanner scanner =new Scanner(System .in ) ;
System.out.println("姓名");
String Name=scanner .next();
System.out.println("分数");
int Mark=scanner .nextInt() ;
System.out.println("Id");
long Id=scanner.nextLong() ;
Course course =new Course(Name ,Mark ,Id ) ;
return course ;
}
}
class Course {
//你的实现代码
private String Name;
private int Mark;
private long Id;
public Course() {
}
public Course(String name, int mark, long id) {
Name = name;
Mark = mark;
Id = id;
}
public String getName() {
return Name;
}
public void setName(String name) {
Name = name;
}
public int getMark() {
return Mark;
}
public void setMark(int mark) {
Mark = mark;
}
public long getId() {
return Id;
}
public void setId(int id) {
Id = id;
}
@Override
public String toString() {
return "Course{" +
"Name='" + Name + '\'' +
", Mark=" + Mark +
", Id=" + Id +
'}';
}
}
