Java数据库JDBC链接

目录

JDBC步骤

常见问题

JDBC查询插入数据

SQL语句

---

JDBC步骤

//注册MySQL驱动 (通常可以省略这一步)
Class.forName("com.mysql.jdbc.Driver");

//连接服务器
String username= "root";
String password = "a1b2c3";
String connectionUrl ="jdbc:mysql://127.0.0.1:3306/af_school?useUnicode=true&characterEncoding=UTF-8";

//测试
Connection conn = DriverManager.getConnection(connectionUrl, username, password); //DriverManger调用connection接口获取三个参数

System.out.println("连接成功!"); //返回
conn.close(); //关闭资源
System.out.println("关闭连接!");

//插入main调用try catch
public statuc void main (String[] args)
{
    try
    {
        testConnect();
    }catch (Exception e)
    {
        e.printStackTrace();
    }
}

常见问题

jdbc:mysql:// 表明连接的是MySQL服务器

127.0.0.1:3306 是服务器的IP和端口

af_school 是数据库名字

useUnicode和characterEncoding参数用于指定交互传输用的字符编码

下载mysql.jar包导入到项目中右键Buid Path

 

JDBC查询插入数据

package my;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

public class Test
{
	public static void testInsert() throws Exception
	{
		/*
		 * JDBC链接数据
		 */
		Class.forName("com.mysql.jdbc.Driver");
		String username = "root";
		String password = "131421xdd";
		String connectionUrl = "jdbc:mysql://127.0.0.1:3306/af_school?useUnicode=true&characterEncoding=UTF-8";
		Connection conn = DriverManager.getConnection(connectionUrl, username, password);
		System.out.println("连接成功!");

		/*
		 * 查询语句
		 */

		// 数据库查询, Statement语句 ResultSet结果集
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM student");

		// 如果有数据，rs.next()返回true
		while (rs.next())
		{
			// 取出这一行记录
			int id = rs.getInt("id");
			String name = rs.getString("name");
			String phone = rs.getString("phone"); // 可能为null
			Date birthday = rs.getDate("birthday");

			System.out.println(id + "\t" + name + "\t" + phone + "\t" + birthday);
		}

		/*
		 * 插入数据
		 */
		String sql = "INSERT INTO student(`id`,`name`,`birthday`) " + "VALUES ('20181200', '韩', '1997-4-19') ";
		System.out.println("SQL: " + sql);

		Statement stmt1 = conn.createStatement();
		stmt1.execute(sql);
		int count = stmt1.getUpdateCount();
		System.out.println("受影响的行数为: " + count);

		//
		conn.close();
		System.out.println("关闭连接!");
	}

	/*
	 * 有自增主键时，可以取回新增的主键的值
	 */
	public static void testInsert2() throws Exception
	{
		// 连接MySQL服务器
		String username = "root";
		String password = "131421xdd";
		String connectionUrl = "jdbc:mysql://127.0.0.1:3306/af_school?useUnicode=true&characterEncoding=UTF-8";

		Connection conn = DriverManager.getConnection(connectionUrl, username, password);
		System.out.println("连接成功!");

		///
		String sql = "INSERT INTO leave_event(`stuId`,`daysFrom`,`daysTo`,`type`,`reason`) "
				+ "VALUES ('20180004', '2018-03-24', '2018-03-30', '2', '出国') ";
		System.out.println("SQL: " + sql);

		Statement stmt = conn.createStatement();
		stmt.execute(sql, Statement.RETURN_GENERATED_KEYS); // 获取主键的值

		// 取得自动生成的主键的值
		ResultSet rs = stmt.getGeneratedKeys();
		while (rs.next())
		{
			int id = rs.getInt(1);
			System.out.println("产生的主键是：" + id);
		}

		//
		conn.close();
		System.out.println("关闭连接!");
	}

	public static void main(String[] args)
	{
		try
		{
			testInsert();
		} catch (Exception e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

SQL语句

/*
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 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=6 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,'不舒服');

/*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 */;