1.首先先导入驱动包,可以去官网下载,然后直接复制粘贴到你的项目里,再右键点击添加为库
2.加载驱动类,通过驱动管理器获取连接对象,代码如下:
2. 加载驱动类
Class.forName("com.mysql.jdbc.Driver");
// 3. 通过驱动管理器获取连接对象 -- Java和数据库建立连接
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/student", "root", "123123");
这里的student为数据库名,root是数据库username,123123是数据库密码
3.创建sql语句
//增删改查 String sql = "insert into score values(123,'456',12)"; String sql = "update score set sno = 123 where sno=666"; String sql = "delete from score where sno=123"; String sql = "select * from student";
4.通过连接对象创建语句对象,用于执行SQL语句
Statement state = conn.createStatement();
ResultSet rs = state.executeQuery(sql);
//处理结果集对象 -->> 打印
while(rs.next()){
System.out.print(rs.getString(1)+"t");
System.out.print(rs.getString(2)+"t");
System.out.print(rs.getString(3)+"t");
System.out.print(rs.getDate(4)+"t");
System.out.println(rs.getString(5));
}
5.关闭连接，释放资源
rs.close(); state.close(); conn.close();
完整代码:
import java.sql.*;
public class Demo1 {
public static void main(String[] args) {
try {
// 1. 导入驱动包
// 2. 加载驱动类
Class.forName("com.mysql.jdbc.Driver");
// 3. 通过驱动管理器获取连接对象 -- Java和数据库建立连接
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/student", "root", "123123");
// 4. 增删改查 --->> 创建sql语句
String sql = "select * from student";
// 5. 通过连接对象创建语句对象 --->> 语句对象是用来执行sql语句
Statement state = conn.createStatement();
// 6. 执行sql语句 executeQuery() ResultSet -- 结果集对象
ResultSet rs = state.executeQuery(sql);
// 7. 处理结果集对象 -->> 打印
while(rs.next()){
System.out.print(rs.getString(1)+"t");
System.out.print(rs.getString(2)+"t");
System.out.print(rs.getString(3)+"t");
System.out.print(rs.getDate(4)+"t");
System.out.println(rs.getString(5));
}
// 8. 关闭连接，释放资源
rs.close();
state.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
在输入条件时可能会使传入的条件的sql字符串拼接后变成另外的sql语句这被称为SQL注入
为了解决这个问题所以使用预处理命令
Statement的问题在于:
- 有SQL注入的危险
- 拼接时不方便
- 效率不高
PreparedStatement:
- 能防止部分SQL注入
- 拼接的时候方便，不用管数据类型
- 在执行多条SQL语句的时候效率要高于Statement
//设置?(通配符)
String sql = "select * from student where sno = ?";
// 创建预编译语句对象
PreparedStatement ps = conn.prepareStatement(sql);
// 设置参数,这里的1代表第一个通配符
ps.setString(1,sno);
// 执行sql语句
ResultSet rs = ps.executeQuery();
while(rs.next()){
//这里创建模型对象是为了存入集合中后续更好的处理信息
Student stu = new Student();
stu.setSno(rs.getString("sno"));
stu.setSname(rs.getString("sname"));
stu.setSsex(rs.getString("ssex"));
stu.setSbirthday(rs.getDate("sbirthday"));
stu.setSclass(rs.getString("class"));
list.add(stu);
}
代码优化:
package demo;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class baseDao {
public static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8";
private static final String USERNAME = "root";
private static final String PASSWORD = "123123";
public static final ArrayList list = new ArrayList<>();
//首先驱动数据库
static {
try{
Class.forName(DRIVER);
}catch (ClassNotFoundException e){
e.printStackTrace();
}
}
public Connection getCon(){
Connection con = null;
try {
con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
public void close(Connection con, Statement state, ResultSet rs){
try {
if (rs!=null) {
rs.close();
}
if (state!=null) {
state.close();
}
if (con!=null) {
con.close();
}
} catch (SQLException sqlException) {
sqlException.printStackTrace();
}
}
public int update(String sql){
Connection conn = getCon();
Statement state = null;
int result = -1;
try {
state = conn.createStatement();
result = state.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(conn,state,null);
}
return result;
}
public int update(String sql,Object ... o){
Connection con = getCon();
PreparedStatement ps = null;
int result = -1;
try{
ps = con.prepareStatement(sql);
for (int i = 0; i < o.length; i++) {
ps.setObject(i+1,o[i]);
}
result = ps.executeUpdate();
}catch (SQLException e){
e.printStackTrace();
}finally {
close(con,ps,null);
}
return result;
}
public void select(String sql){
Connection con = getCon();
PreparedStatement ps = null;
ResultSet rs = null;
try{
ps = con.prepareStatement(
sql, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
rs = ps.executeQuery();
while (rs.next()){
//建立学生对象
Student student = new Student();
//设置信息
student.setSno(rs.getString("sno"));
student.setSbirthday(rs.getDate("sbirthday"));
student.setSclass(rs.getString("class"));
student.setSname(rs.getString("sname"));
student.setSsex(rs.getString("ssex"));
//存如集合中
list.add(student);
}
//元数据操作
ResultSetmetaData rsmd = rs.getmetaData();
//输出列名和列的数据类型
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
System.out.print(rsmd.getColumnName(i)+"t"+"t"+"t");
if (i==rsmd.getColumnCount()){
System.out.println();
}
}
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
System.out.print(rsmd.getColumnTypeName(i)+"t"+"t"+"t");
if (i==rsmd.getColumnCount()){
System.out.println();
}
}
//打印信息
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i).getSno()+"t"+"t"+"t"
+list.get(i).getSname()+"t"+"t"+"t"
+list.get(i).getSsex()+"t"+"t"+"t"
+list.get(i).getSbirthday()+"t"+"t"+"t"
+list.get(i).getSclass());
}
}catch (SQLException e){
e.printStackTrace();
}finally {
close(con,ps,rs);
}
}
}
