1.Staement执行DDL语句
创建表
package com.huan.statemennt;
import com.mysql.jdbc.Driver;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Created by pc on 17-5-1.
*/
public class StatementDemo {
String user = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/eurasia_echarts";
Connection conn = null;
Statement state = null;
@Test
public void test1() throws Exception {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
if (null != conn) {
System.out.println("数据库连接成功");
}
//创建statement对象
state = conn.createStatement();
//创建sql语句
String sql = " create table mahuan(id int primary key auto_increment ,name varchar(20),age int); ";
//发送sql语句，执行并返回结果
int count = state.executeUpdate(sql);
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭连接
if (null != state) {
state.close();
}
if (null != conn) {
conn.close();
}
}
}
}
2.staement执行执行DML语句
-
插入数据
-
更新数据
-
删除数据
Dao.java
package com.huan.dao;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Created by pc on 17-5-1.
*/
public class Dao {
static String user ="root";
static String password = "root";
static String url = "jdbc:mysql://localhost:3306/eurasia_echarts?characterEncoding=UTF-8";
@Test
public static Connection getDao() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, user, password);
if (null != conn) {
System.out.println("连接成功");
}
return conn;
}
public static void close(Statement state, Connection conn){
if(null != state){
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != conn){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Dml_Demo.java
package com.huan.statemennt;
import com.huan.dao.Dao;
import org.junit.Test;
import java.sql.Connection;
import java.sql.Statement;
/**
* Created by pc on 17-5-1.
*/
public class Dml_Demo {
String user ="root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/eurasia_echarts?characterEncoding=UTF-8";
Connection conn = null;
Statement state = null;
@Test
public void test() throws Exception{
conn =Dao.getDao();
state = conn.createStatement();
//插入数据
String sql = " insert into mahuan (name,age) values('小花',15); ";
//更新数据
//String sql = " update mahuan set name ='马欢' where id =3; ";
//删除数据
// String sql = " delete from mahuan where id = 3; ";
int count = state.executeUpdate(sql);
System.out.println(count);
Dao.close(state,conn);
}
}
3.staement执行DQL语句
查询数据
package com.huan.statemennt;
import com.huan.dao.Dao;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
/**
* Created by pc on 17-5-1.
*/
public class Dql_demo {
@Test
public void test() throws Exception {
Connection conn = null;
Statement stmt = null;
conn = Dao.getDao();
stmt = conn.createStatement();
String sql = " select * from mahuan; ";
//接收查询到的所有成绩
ResultSet rs =stmt.executeQuery(sql);
while(rs.next()){
System.out.println("用索引取值：编号："+rs.getInt(1) +" 姓名："+rs.getString(2)+" 年龄："+rs.getString(3));
System.out.println("用列名称取值：编号："+rs.getInt("id") +" 姓名："+rs.getString("name")+" 年龄："+rs.getString("age"));
}
Dao.close(stmt,conn);
}
}
