实验4 JDBC编程（2学时）

实验目的

1. 掌握Java＋MySQL数据库的连接及访问方式。
2. 掌握Java＋MySQL数据库的查询、添加、删除和修改等操作。

实验任务

1. 创建一个数据库userdb，含user表，该表用于存放用户注册的信息：
   user ( username nchar(20) not null primary key, psd char(32))。
   
2. 使用JDBC完成用户注册功能，界面如下：
   
   其中：
   “*”表示必填，密码和确认密码要一致。
   “注册”按钮：注册前要检测用户名是否存在，如果不存在，才将用户名和密码（要求使用32位md5加密）存入user表。

import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.ArrayList;
import java.io.*;

public class Window extends JFrame {
	private JFrame jFrame = new JFrame("用户注册");
	private Container c = jFrame.getContentPane();
	private JLabel a1 = new JLabel("用户名：");
	private JLabel b1 = new JLabel("*");
	private JTextField name = new JTextField();
	private JLabel a2 = new JLabel("密码：");
	private JLabel b2 = new JLabel("*");
	private JLabel a3 = new JLabel("请再次输入密码：");
	private JLabel b3 = new JLabel("*");
	private JButton submit = new JButton("注册");
	private JButton afresh = new JButton("重输");
	private JPasswordField passport;
	private JPasswordField passport2;

	public Window() {
		// 设置窗体的位置及大小
		jFrame.setBounds(200, 200, 380, 200);
		// 设置一层相当于桌布的东西
		c.setLayout(new BorderLayout());// 布局管理器
		// 设置按下右上角X号后关闭
		jFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		// 初始化--往窗体里放其他控件
		init();
		// 设置窗体可见
		jFrame.setVisible(true);
	}
	 
	public void init() {

		/* 输入部分--Center */
		JPanel fieldPanel = new JPanel();
		fieldPanel.setLayout(null);
		a1.setBounds(80, 20, 55, 20);
		a2.setBounds(92, 50, 50, 20);
		a3.setBounds(28, 80, 120, 20);
		b1.setBounds(140, 22, 20, 20);
		b1.setForeground(Color.red);
		b2.setBounds(140, 52, 20, 20);
		b2.setForeground(Color.red);
		b3.setBounds(140, 82, 20, 20);
		b3.setForeground(Color.red);
		fieldPanel.add(a1);
		fieldPanel.add(a2);
		fieldPanel.add(a3);
		fieldPanel.add(b1);
		fieldPanel.add(b2);
		fieldPanel.add(b3);
		name.setBounds(150, 20, 160, 20);
		fieldPanel.add(name);
		c.add(fieldPanel, "Center");
		
		passport = new JPasswordField();
		passport.setBounds(150, 50, 160, 21);
		fieldPanel.add(passport);
		
		passport2 = new JPasswordField();
		passport2.setBounds(150, 80, 160, 21);
		fieldPanel.add(passport2);

		/* 按钮部分--South */
		JPanel buttonPanel = new JPanel();
		buttonPanel.setLayout(new FlowLayout());
		buttonPanel.add(submit);
		// 提交按下去获取
		submit.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				if(name.getText().toString().equals("")||passport.getText().toString().equals("")||passport2.getText().toString().equals("")) {
					JOptionPane.showMessageDialog(null, "请保证必填处有被填充");
				}
				else {
					if (passport.getText().toString().equals(passport2.getText().toString())) {
					     User newUser = new User();
					     newUser.setUsername(name.getText());
					     newUser.setPassword(new MD5_Demo().MD5(passport.getText()));
					     CheckRegister newCheckRegister = new CheckRegister();
					     newCheckRegister.RegisterUser(newUser);
				    } else {
					     JOptionPane.showMessageDialog(null, "两次输入密码不同");
				    }
				}
			}
		});
		buttonPanel.add(afresh);
		// 重填按下去清空
		afresh.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				name.setText("");
				passport.setText("");
				passport2.setText("");
			}
		});
		c.add(buttonPanel, "South");
	}

	// 测试
	public static void main(String[] args) {
		Window stu = new Window();
	}
}

public class User { // 用户类 
	private String username = null;
	private String userpassword = null;

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return userpassword;
	}

	public void setPassword(String userpassword) {
		this.userpassword = userpassword;
	}
}

import java.sql.*;
import javax.swing.JOptionPane;

public class CheckRegister {
	public static void RegisterUser(User u) {

//		Connection conn = null;
//		PreparedStatement ps = null;
//		ResultSet rs = null;

		try {

//			// 注册驱动
//			Class.forName("com.mysql.cj.jdbc.Driver");
//
//			// 获取数据库连接
//			String url = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";
//			String user = "root";
//			String password = "201813136045";
//			conn = DriverManager.getConnection(url, user, password);
//
//			String sqlto = "select * from tb_user where username=?";
			PreparedStatement pStmt = conn.prepareStatement(sqlto);
			pStmt.setString(1,u.getUsername());
			rs=pStmt.executeQuery();  
//			Statement s = conn.createStatement();
//			rs = s.executeQuery(sqlto);
//
//			// 创建sql框架
//			String sql = "insert into tb_user(username,psd) values(?,?)";
//
//			// sql预编译
//			ps = conn.prepareStatement(sql);
//
//			// sql赋值
//			ps.setString(1, u.getUsername());
//			ps.setString(2, u.getPassword());
//
//			// 执行sql
//		     while (rs.next()) {
//				if (rs.getString("username").equals(u.getUsername())) {
//					JOptionPane.showMessageDialog(null, "注册名存在");
//				} else {
//					ps.executeUpdate();
//					JOptionPane.showMessageDialog(null, "注册成功");
//				}
//			}

			String url = "jdbc:mysql://localhost:3306/userdb?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true"; // 数据库连接字符串
			Class.forName("com.mysql.cj.jdbc.Driver"); // 加载驱动程序
			Connection conn = DriverManager.getConnection(url, "root", "201813136045"); // 建立连接

			String sql = "select * from user where username=?";
			PreparedStatement pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, u.getUsername());
			ResultSet rs = pStmt.executeQuery();
			if (rs.next()) {
				JOptionPane.showMessageDialog(null, "注册名存在");
			} else {
				sql = "insert into user values(?,?)";
				pStmt = conn.prepareStatement(sql);
				pStmt.setString(1, u.getUsername());
				pStmt.setString(2, u.getPassword());
				pStmt.executeUpdate();
				JOptionPane.showMessageDialog(null, "注册成功");
			}

		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "注册失败");
		}

//		finally {// 关闭流
//			if (P != null) {
//				try {
//					ps.close();
//				} catch (SQLException e) {
//					e.printStackTrace();
//				}
//			}
//			if (conn != null) {
//				try {
//					conn.close();
//				} catch (SQLException e) {
//					e.printStackTrace();
//				}
//			}
//		}

	}

}

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;


public class MD5_Demo {
	
	public final static String MD5(String s){
		char hexDigits[]={'0','1','2','3','4','5','6','7','8','9','a','b','c','d','e','f'};
		
		try {
			//把字符串转换成字节码的形式
			byte[] strTemp=s.getBytes();
			//申明mdTemp为MD5加密的形式
			MessageDigest mdTemp=MessageDigest.getInstance("MD5");
			//进行字节加密并行进加密 转化成16位字节码的形式
			mdTemp.update(strTemp);
			byte[] md=mdTemp.digest();
			//j=32 
			int j=md.length;
			char str[]=new char[j*2];
			int k=0;
			//对字符串进行重新编码成32位的形式
			for(int i=0;i<j;i++){
				byte byte0=md[i];
				str[k++]=hexDigits[byte0>>>4 & 0xf];
				str[k++]=hexDigits[byte0 & 0xf];
			}
			return new String(str);
						
		} catch (NoSuchAlgorithmException e) {
			return null;			
		}
		
	}
}