JDBC连接数据库

JDBC连接步骤

1.加载驱动
2.创建连接
3.获取statement
4.执行sql
5.关闭连接

JDBCBasic

创建连接与关闭连接写成静态方法，方便调用

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

public class JDBCBasic {

	
	public static Connection getConnection() {
		Connection conn =null;
		
			try {
				Class.forName("com.mysql.cj.jdbc.Driver");
				//java10是数据库的名字
				String url="jdbc:mysql://localhost:3306/java11?useSSL=false&serverTimezone=Asia/Shanghai";
				//登录数据库用户名
				String username="root";
				//登录数据库密码
				String pwd="hykjc3F2020";
				conn = DriverManager.getConnection(url,username,pwd);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
	
		
		return conn;
		
	}
	
	
	public static void closeConn(Statement st,ResultSet rs,Connection conn) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(st!=null) {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if( conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
}

学生表属性

创建一个学生类里面存放学生表中的属性

public class Student {
	private int id; 
	private String sex;   
	private String cname; 
	private String mobile;
	private int score;
	private String name;
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getCname() {
		return cname;
	}
	public void setCname(String cname) {
		this.cname = cname;
	}
	public String getMobile() {
		return mobile;
	}
	public void setMobile(String mobile) {
		this.mobile = mobile;
	}
	public int getScore() {
		return score;
	}
	public void setScore(int score) {
		this.score = score;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", sex=" + sex + ", cname=" + cname + ", mobile=" + mobile + ", score=" + score
				+ ", name=" + name + "]";
	}
	
	

}

创建学生表服务类

与数据库连接执行各种操作

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

public class StudentService {
	
	public List<Student> getAllStudent(String sql){
		List<Student> list=new ArrayList();
		Connection conn=JDBCBasic.getConnection();
		try {
			Statement st=conn.createStatement();
			ResultSet  rs=st.executeQuery(sql);
			while(rs.next()) {//***这里要注意rs.next()表示有数据传入则为true，while循环获取学生表中信息***
				Student student=new Student();
				student.setName(rs.getString("name"));
				student.setId(rs.getInt("id"));
				student.setScore(rs.getInt("score"));
				student.setSex(rs.getString("sex"));
				list.add(student);//***不要忘记在list集合中添加student信息！！！！***
			}
			
			JDBCBasic.closeConn(st, rs, conn);//***注意关闭数据库***
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	
	public Student add(Student student) {
		Connection conn=JDBCBasic.getConnection();
		try {
			Statement st=conn.createStatement();
			
			Random r=new Random();
			int id=r.nextInt(1000);
			student.setId(id);
			String sql="insert into student (id,sex,score,name) values ("+id+",'"+student.getSex()+"',"+student.getScore()+",'"+student.getName()+"')";
			st.execute(sql);
			JDBCBasic.closeConn(st, null, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
		return student;
	}
	
	
	public boolean updateNameById(String name,int id) {
		Connection conn=JDBCBasic.getConnection();
		boolean flag=false;
		try {
			Statement st=conn.createStatement();
			
			
			String sql="update student set name='"+name+"' where id="+id;
			if(st.executeUpdate(sql)>0) {//这里意思是如果跟新行数大于0就进入循环
				flag=true;
			};
			JDBCBasic.closeConn(st, null, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
		return flag;
	}
	
	
	public boolean deleteByName(String name) {
		Connection conn=JDBCBasic.getConnection();
		boolean flag=false;
		try {
			Statement st=conn.createStatement();
			String sql="delete from student where name='"+name+"'";
			if(st.executeUpdate(sql)>0) {
				flag=true;
			};
			JDBCBasic.closeConn(st, null, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
}