javaweb基础篇-jdbc简单连接mysql,创表&增删改查

首先需要将mysql的驱动包导入java项目:
新建名为lib的folder,拖入驱动jar包
然后buildpath进项目

---

接下来开始写代码
1.加载注册驱动
2.获取连接对象

package jdbc_connectionDemo;

import java.sql.Connection;
//注意这里导包是用java中的sql包的接口而不是mysql包的实现类
import java.sql.DriverManager;

import org.junit.Test;

//获取数据库连接对象
public class ConnectionTest {
    @Test
    public void test1() throws Exception {
        //1.加载注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");//此时参数为数据库的url,账号,密码
    }
}

Class.forName("");方法 的作用是 要求JVM查找并加载指定的类,在数据库使用中用来加载数据库的驱动包
在开发中这个方法常用于工厂设计模式创建对象

从jdk6开始可以不用手动加载注册(不推荐,且JavaWeb中不可用)

---

创表操作

package jdbc_connectionDemo;

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

import org.junit.Test;


//获取数据库连接对象
public class ConnectionTest {
    @Test
    public void test1() throws Exception {
        //1.加载注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");//此时参数为数据库的url,账号,密码
        //3.创建语句对象
        Statement st = conn.createStatement();
        //4.执行sql语句
        st.executeUpdate(
                "CREATE TABLE `t_student` (\r\n" + 
                "  `id` bigint(20) NOT NULL,\r\n" + 
                "  `name` varchar(255) DEFAULT NULL,\r\n" + 
                "  `age` int(11) DEFAULT NULL,\r\n" + 
                "  PRIMARY KEY (`id`)\r\n" + 
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
        //5.释放资源(后开先关)
        st.close();
        conn.close();
    }
}

---

结果如下

---

接下来进行增删改操作

public class DMLTest {
    @Test
    //增加表内列
    public void testInsert() throws Exception {
        String sql = "INSERT INTO t_student (name,age) VALUES ('小万',21)";
        //1.加载注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","admin");
        //3.创建语句对象
        Statement st = conn.createStatement();
        //4.执行sql操作
        st.executeUpdate(sql);
        //5.释放资源
        st.close();
        conn.close();
    }
    @Test
    //更新列
    public void testUpdate() throws Exception {
        String sql = "UPDATE t_student SET name = '小童',age = 20 WHERE id = 1";
        //1.加载注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","admin");
        //3.创建语句对象
        Statement st = conn.createStatement();
        //4.执行sql操作
        st.executeUpdate(sql);
        //5.释放资源
        st.close();
        conn.close();
    }
    @Test
    //删除列
    public void testDelete() throws Exception {
        String sql = "Delete FROM t_student WHERE id = 1";
        //1.加载注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","admin");
        //3.创建语句对象
        Statement st = conn.createStatement();
        //4.执行sql操作
        st.executeUpdate(sql);
        //5.释放资源
        st.close();
        conn.close();
    }
}

查询操作较为不同:

package jdbc_DQL;

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

import org.junit.Test;

public class DQLTest {
    @Test
    public void testQuery() throws Exception {
        String sql = "SELECT * FROM t_student";
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()) {
            long id = rs.getLong("id");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            System.out.println("学生id:"+id+",学生名字:"+name+",学生年龄:"+age);
        }
        rs.close();
        st.close();
        conn.close();
    }
}

执行结果如下:

查询相对麻烦的步骤在于对表查询后信息的处理
用executeQuery返回的是一个查询结果集,用ResultSet来接收

ResultSet rs = st.executeQuery(sql);
        while (rs.next()) {
            long id = rs.getLong("id");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            System.out.println("学生id:"+id+",学生名字:"+name+",学生年龄:"+age);
        }