SQL
数据库
DATABASE 数据库
TABLE 表
字段 (属性)
主键(primary key):一个表中不能重复,能够使用这个值确定唯一一条数据
外键:一个表中的数据持有的其他表的值
package com.java.sql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test1 {
public static void main(String[] args) {
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/class";
String user="root";
String password="123456";
try {
Class.forName(driver);
Connection con=DriverManager.getConnection(url,user,password);
if(!con.isClosed()){
Statement statement=con.createStatement();
String insert="insert into student(id,name,sex,age)value(1,'张三',1,20)";
statement.execute(insert);
String update="update student set age=25 where id=2";
statement.execute(update);
String delete="delete from student where name='李四'";
String select="select * from student";
ResultSet set=statement.executeQuery(select);
set.first();
while(!set.isAfterLast()){
System.out.println(set.getString("name"));
set.next();
}
System.out.println("执行成功");
}else{
System.out.println("打开数据库");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
用户的注册与登录
package com.java.sql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLManager {
private Statement statement;
public Statement getStatement() {
return statement;
}
public void setStatement(Statement statement) {
this.statement = statement;
}
private static SQLManager manager;
public static SQLManager newInstance(){
if(manager==null){
manager=new SQLManager();
}
return manager;
}
private SQLManager(){
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/class";
String user="root";
String password="123456";
try {
Class.forName(driver);
Connection con=DriverManager.getConnection(url,user,password);
if(!con.isClosed()){
statement=con.createStatement();
statement.execute("create table if not exists user(num int not null primary key auto_increment,user_name varchar(20),password varchar(20))");
}else{
System.out.println("打开数据库");
}
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
注册
package com.java.sql;
public class Test2 {
public static void main(String[] args) {
SQLManager manager=SQLManager.newInstance();
}
}
JButton btnNewButton = new JButton("注册");
btnNewButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
String userName=textField.getText();
String password=textField_1.getText();
Statement state=SQLManager.newInstance().getStatement();
String sql="select * from user where user_name='"+userName+"'";
try {
ResultSet set=state.executeQuery(sql);
set.last();
int num=set.getRow();
if(num>0){
System.out.println("用户已存在");
}else{
String register="insert into user(user_name,password)values('"+userName+"','"+password+"')";
state.execute(register);
System.out.println("注册成功");
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
});
登录
JButton btnNewButton = new JButton("登录");
btnNewButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
String userName=textField.getText();
String password=textField_1.getText();
Statement statement=SQLManager.newInstance().getStatement();
String sql="select * from user where user_name='"+userName+"'and password='"+password+"'";
try {
ResultSet set=statement.executeQuery(sql);
set.last();
int num=set.getRow();
System.out.println(num);
} catch (SQLException e1) {
e1.printStackTrace();
}
}
});