Java中sql语句操作数据库及将数据库信息显示在表格中

首先连接数据库，接着就可以对数据库进行操作了

创建Connection对象，建立与数据库的连接。

ResultSet 对象是用来存放操作结果的，也就是SQL查询语句对数据库的查询操作将返回一个ResultSet 对象，

ResultSet 对象一次只能看到一行数据，使用next（）方法移到下一个数据行。

PreparedStatement （ps）方法对参数sql指定的SQL语句进行预编译处理，生成该数据库底层的内部指令。

ps.executeQuery()用于产生单个结果集的语句，返回ResultSet对象，用来执行select语句

ps.executeUpdate()用于更新，用于执行insert，delete，update等SQL语句

.close()关闭连接

向数据库中插入注册信息：

public void insert(String username,String password,String name) throws SQLException {
			ResultSet rs ;
			Connection con;
			PreparedStatement ps;
			
			ConnectData cd = new ConnectData();
			con = cd.connect();		
			String sql = "insert into user values(?,?,?)";
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			ps.setString(3, name);
			int ok = ps.executeUpdate();
			con.close();
		}

 

将全部图书输出在表中：

public void selectAll(DefaultTableModel model)throws Exception{
		String sql = "select * from bookmanage";
		Connection con;
		ResultSet rs;
		PreparedStatement ps;
		ConnectData cd = new ConnectData();
		con = cd.connect();
		ps = con.prepareStatement(sql);
		rs = ps.executeQuery();
		while(rs.next()) {
			int BookNo = rs.getInt(1);
			String Book = rs.getString(2);
			String Author = rs.getString(3);
			String Lend = rs.getString(4);
			model.addRow(new Vector<>(Arrays.asList(BookNo,Book,Author,Lend)));
		}
		con.close();
	}

 

按照书名模糊查询并输出在表格中：

public void bookSelect(DefaultTableModel model,String book) throws Exception {
		String sql = "select * from bookmanage where Book like ?";
		Connection con;
		ResultSet rs;
		PreparedStatement ps;
		ConnectData cd = new ConnectData();
		con = cd.connect();
		ps = con.prepareStatement(sql);
		book= "%"+book+"%";
		ps.setString(1, book);
		rs = ps.executeQuery();
		while(rs.next()) {
			int BookNo = rs.getInt(1);
			String Book = rs.getString(2);
			String Author = rs.getString(3);
			String Lend = rs.getString(4);
			model.addRow(new Vector<>(Arrays.asList(BookNo,Book,Author,Lend)));
		}
		con.close();
	}

更新图书状态信息：

public void updatelend(DefaultTableModel model,String book) throws Exception{
		String sql = "update bookmanage set Lend = '否' where Book = ?";
		Connection con;
		PreparedStatement ps;
		ConnectData cd = new ConnectData();
		con = cd.connect();
		ps = con.prepareStatement(sql);
		ps.setString(1, book);
		int ok = ps.executeUpdate();
		con.close();
		model.setRowCount(0);
		selectAll(model);
		con.close();
	}