Java连接数据库

Java连接数据库

下载相关jar包

在连接数据库之前我们需要下载一个jar包，这个jar包是用来提供mysql驱动的，这里我给出这个jar包下载链接：支持JDBC驱动jar包

IDE中引用jar包

IDE(集成化开发环境)
java语言常见的IDE有 eclipse、IDEA
我们就以这两个为例
eclipse:
点击项目文件夹->右键->新建文件夹(命名为lib好了)
如图所示：

然后把jar包复制到文件夹中
右键->添加到构建路径

然后就大功告成

IDEA
点击工具栏中 file->Project Structure

然后进入这个界面

点击右边的+号选择jar文件所在路径
添加成功后，我们就可以点击右下角的ok按钮

建议大家学习java时候使用IDEA进行学习，eclipse现在用的比较少了。
IDEA逐渐成为主流IDE
这里我给出IDEA下载地址：IDEA下载地址

连接数据库代码

在连接数据库之前我们先要有一个数据库
这里我给大家一个数据库
也就是下面这些SQL语句代码了，大家把它粘贴到你的SQLyog或者Navicat上运行下就有这个数据库了
下面我们都根据这个数据库进行练习

/*
SQLyog Ultimate v11.24 (32 bit)
MySQL - 5.6.39 : Database - af_school
*********************************************************************
*//*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=''*/;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`af_school` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `af_school`;/*Table structure for table `exam` */DROP TABLE IF EXISTS `exam`;CREATE TABLE `exam` (`id` int(11) NOT NULL COMMENT '学号',`chinese` int(11) DEFAULT NULL COMMENT '语文成绩',`english` int(11) DEFAULT NULL COMMENT '英语成绩',`math` int(11) DEFAULT NULL COMMENT '数学成线',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `exam` */insert  into `exam`(`id`,`chinese`,`english`,`math`) values (20180001,89,90,98),(20180002,78,82,93),(20180003,90,73,95),(20180004,88,98,83),(20180005,96,79,75),(20180006,77,98,82);/*Table structure for table `leave_event` */DROP TABLE IF EXISTS `leave_event`;CREATE TABLE `leave_event` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '请假记录ID',`stuId` int(11) NOT NULL COMMENT '学生ID',`daysFrom` date DEFAULT NULL COMMENT '哪天开始',`daysTo` date DEFAULT NULL COMMENT '哪天结束',`type` tinyint(4) DEFAULT NULL COMMENT '类型，0病假，1事假',`reason` varchar(256) DEFAULT NULL COMMENT '事由',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;/*Data for the table `leave_event` */insert  into `leave_event`(`id`,`stuId`,`daysFrom`,`daysTo`,`type`,`reason`) values (1,20180001,'2018-01-02','2018-01-03',0,'感冒'),(2,20180001,'2018-03-06','2018-03-08',0,'发烧'),(3,20180003,'2018-03-01','2018-03-07',1,'出国旅游'),(4,20180005,'2018-03-07','2018-03-07',1,'家里有事'),(5,20180003,'2018-03-17','2018-03-18',0,'不舒服'),(6,20180004,'2018-03-24','2018-03-30',2,'出国'),(7,20180004,'2018-03-24','2018-03-30',2,'出国'),(8,20180004,'2018-03-24','2018-03-30',2,'出国');/*Table structure for table `student` */DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`id` int(11) NOT NULL COMMENT '学号',`name` varchar(32) NOT NULL COMMENT '姓名',`sex` tinyint(1) DEFAULT NULL COMMENT '性别',`phone` varchar(16) DEFAULT '13800000000' COMMENT '手机号',`birthday` date DEFAULT NULL COMMENT '生日',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `student` */insert  into `student`(`id`,`name`,`sex`,`phone`,`birthday`) values (20180001,'盖聂',1,'1409900089','1982-03-09'),(20180002,'卫庄',1,'1282399999','1993-10-01'),(20180003,'张良',1,NULL,'1996-03-11'),(20180004,'伏念',1,NULL,'1982-09-24'),(20180005,'颜路',1,'13699292899','1983-11-21'),(20180006,'赤练',0,'13819289890','1998-03-12'),(20180007,'端木蓉',0,'13800000000','1978-05-12'),(20180008,'盗跖',1,'13410012908','1993-09-10'),(20180009,'白凤',1,'13509890090','1994-04-20'),(20180010,'天明',1,'18799891829','2002-04-19'),(20180011,'月儿',0,'13882938990','2003-06-10');/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

测试连接数据库（不执行SQL语句）

package company;import java.sql.Connection;
import java.sql.DriverManager;public class Main {public static void testConnect() throws Exception{// 注册MySQL驱动 (可以省略这一步)Class.forName(&#sql.jdbc.Driver");// 连接MySQL服务器String username= "root";String password = "";String connectionUrl = "jdbc:mysql://127.0.0.1:3306/af_school?useUnicode=true&characterEncoding=UTF-8";Connection conn = Connection(connectionUrl, username, password);System.out.println("连接成功!");conn.close();System.out.println("关闭连接!");}public static void main(String[] args) throws Exception {testConnect();}
}

连接数据库并执行查询语句

package company;import java.sql.*;
import java.util.Date;public class Main2 {public static void linktest() throws Exception{String username= "root";String password = "";String connectionUrl = "jdbc:mysql://127.0.0.1:3306/af_school?useUnicode=true&characterEncoding=UTF-8";Connection conn = Connection(connectionUrl,username,password);Statement state&#ateStatement();ResultSet resultSet&#uteQuery("select * from student");System.out.println("链接成功");System.out.println("学号      t"+"姓名 t"+"性别     t"+"电话 t"+"生日     t");while (()){String stuID&#String("id");String stuName&#String("name");int sex&#Int("sex");String phoneNumber&#String("phone");Date date&#Date("birthday");System.out.println(stuID+"t"+stuName+"t   "+sex+"t"+phoneNumber+"t"+date);}System.out.println("查询结束");conn.close();System.out.println("关闭链接");}public static void main(String[] args) throws SQLException {try {linktest();} catch (Exception e) {e.printStackTrace();}}
}

连接数据库并执行插入语句

package company;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;public class Main3 {public static void link_JDBC() throws Exception {Class.forName(&#sql.jdbc.Driver");String username="root";String password="";String conURL="jdbc:mysql://127.0.0.1:3306/af_school?" +"useUnicode=true&characterEncodeing=UTF-8";Connection con= Connection(conURL,username,password);System.out.println("连接成功!");Statement statement&#ateStatement();ute("insert into student(id,name,sex,phone) values ('20180012','程麒阁',1,'13252702178')");int count&#UpdateCount();System.out.println("受影响行数为："+count);con.close();System.out.println("关闭连接！");}public static void main(String[] args) {try {link_JDBC();} catch (Exception e) {e.printStackTrace();}}
}

连接数据库并执行删除语句

package company;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;public class Main3 {public static void link_JDBC() throws Exception {Class.forName(&#sql.jdbc.Driver");String username="root";String password="";String conURL="jdbc:mysql://127.0.0.1:3306/af_school?" +"useUnicode=true&characterEncodeing=UTF-8";Connection con= Connection(conURL,username,password);System.out.println("连接成功!");Statement statement&#ateStatement();ute("delete from student where id='20180011'");int count&#UpdateCount();System.out.println("受影响行数为："+count);con.close();System.out.println("关闭连接！");}public static void main(String[] args) {try {link_JDBC();} catch (Exception e) {e.printStackTrace();}}
}

连接数据库并修改数据

package company;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;public class Main3 {public static void link_JDBC() throws Exception {Class.forName(&#sql.jdbc.Driver");String username="root";String password="";String conURL="jdbc:mysql://127.0.0.1:3306/af_school?" +"useUnicode=true&characterEncodeing=UTF-8";Connection con= Connection(conURL,username,password);System.out.println("连接成功!");Statement statement&#ateStatement();ute("update student set name='小白' where id='20180012'");int count&#UpdateCount();System.out.println("受影响行数为："+count);con.close();System.out.println("关闭连接！");}public static void main(String[] args) {try {link_JDBC();} catch (Exception e) {e.printStackTrace();}}
}