jdbc实现数据库的增删改查

最新推荐文章于 2022-05-14 11:33:48 发布

亦青-张震刚

最新推荐文章于 2022-05-14 11:33:48 发布

阅读量425

点赞数 1

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： jdbc

本文链接：https://blog.youkuaiyun.com/a_bule_boy/article/details/79611896

数据库专栏收录该内容

0 篇文章

订阅专栏

本文详细介绍了如何利用JDBC进行数据库的增、删、改、查操作，结合具体的user、SC和Course表格实例，展示了基本的SQL语句和JDBC编程步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

package cpm.ytu.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import com.mysql.jdbc.Statement;
public class JDBCTest {//实现数据连接
	public static Connection getConnection() {
		Connection conn=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db","root","1234");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	public static void insert() {//数据表中插入数据
		Connection conn=getConnection();
		try {
			String sql="insert into tal_user(id,name,password,email)"
					+ "values(5,'pet','234','36@178.com')";
			Statement st= (Statement) conn.createStatement();
			int count =st.executeUpdate(sql);
			System.out.println("向用户表插入了"+count+"条记录");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static void delete() {//删除数据表中数据
		Connection conn=getConnection();
		try {
			String sql="delete from tal_user where name='xiaoxiao'";
			Statement st=(Statement) conn.createStatement();
			int count =st.executeUpdate(sql);
			System.out.println("向用户表删除了"+count+"条记录");
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static void update() {//更新数据表中数据
		Connection conn=getConnection();
		try {
			String sql="update tal_user set email='@qq.com' where name='huanahun'";
			Statement st=(Statement) conn.createStatement();
			int count =st.executeUpdate(sql);
			System.out.println("向用户表更新了"+count+"条记录");
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static void select() {//查看用户表信息
		Connection conn=null;
		Statement st=null;
		ResultSet rs=null;
		String sql="select *from tal_user, Course,SC where tal_user.id=SC.id and Course.cno=SC.cno";
		 
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db","root","1234");
			System.out.println("查看用户表信息");
			st=(Statement) conn.createStatement();
			rs=st.executeQuery(sql);
			while(rs.next()) {
				System.out.print(rs.getInt("id")+" ");
				System.out.print(rs.getString("name")+" ");
//				System.out.print(rs.getString("password")+" ");
//				System.out.print(rs.getString("email")+" ");
//				System.out.print(rs.getInt("cno")+" ");
				System.out.print(rs.getString("cname")+" ");
				System.out.print(rs.getInt("grade")+" ");
				System.out.print(rs.getInt("Ccredit")+" ");
				System.out.println();
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				rs.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			try {
				st.close();
			} catch (Exception e3) {
				e3.printStackTrace();
			}
			try {
				conn.close();
			} catch (Exception e4) {
				e4.printStackTrace();
			}
		}
	}
    public static void main(String[] args) {
    	//insert();
    	//delete();
    	//update();
    	select();

    }
}

对应建的数据库table如下：

user：

create table tal_user(
id int(11) unsigned not null auto_increment,
name varchar(50) not null default '',
password varchar(50) not null default '',
email varchar(50) default '',
primary key(id))
engine=InnoDB
default charset=utf8;
select *from tal_user;
insert into tal_user(id,name,password,email) 
values
(1,'xiaoxiao','1234','1234@.com'),
(2,'huanahun','1234','163@.com');
insert into tal_user values(4,'Cat','123','@163.com');

SC：

create table SC(
id int(20) not null auto_increment,
cno int(20) not null,
grade int(20) not null,
primary key(id,cno))
engine=InnoDB
default charset=utf8;

insert into SC(id,cno,grade)
values
(1,'1','90'),
(1,'2','89'),
(1,'3','88'),
(2,'1','79'),
(2,'4','99'),
(2,'3','77'),
(3,'1','68'),
(3,'3','80');

Course：

create table Course(
cno int(20),
Cname varchar(50),
Ccredit smallint,
primary key(cno))

engine=InnoDB
default charset=utf8;
insert into Course(cno,Cname,Ccredit)
values(1,'数据库','99'),
(2,'数学','89'),
(3,'操作系统','90'),
(4,'英语','88');
select *from Course;
update Course set Ccredit='4' where cno=1;
update Course set Ccredit='4' where cno=2;
update Course set Ccredit='3' where cno=3;
update Course set Ccredit='2' where cno=4;

至此一个简单的数据表的增删改查功能就完成了。