<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/spring-boot-demo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
username: root
password: 1234qwer
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
initialization-mode: always
continue-on-error: true
schema:
- "classpath:db/schema.sql"
data:
- "classpath:db/data.sql"
hikari:
minimum-idle: 5
connection-test-query: SELECT 1 FROM DUAL
maximum-pool-size: 20
auto-commit: true
idle-timeout: 30000
pool-name: SpringBootDemoHikariCP
max-lifetime: 60000
connection-timeout: 30000
package com.ldd.service;
import com.ldd.domain.User;
import javax.sql.DataSource;
import java.util.List;
public interface UserService {
/*User表*
* 创建
* */
public void createtable();
/**
* 新增一个用户
* @param name
* @param age
*/
void create(Long id,String name, Integer age);
/**
* 根据name删除一个用户高
* @param name
*/
void deleteByName(String name);
/**
* 获取用户总量
*/
Integer getAllUsers();
/**
* 删除所有用户
*/
void deleteAllUsers();
/**
* This is the method to be used to initialize
* database resources ie. connection.
*/
public void setDataSource(DataSource ds);
/**
* This is the method to be used to list down
* all the records from the Student table.
*/
public List<User> ListUser();
}
package com.ldd.service;
import com.ldd.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import javax.sql.DataSource;
import java.util.List;
@Service("UserServiceImpl")
public class UserServiceImpl implements UserService{
/*CREATE TABLE Person(
ID int generated by default as identity (start with 1) not null,
FIRSTNAME VARCHAR(20) NOT NULL,
LASTNAME VARCHAR(20) NOT NULL,
PRIMARY KEY (ID)
);*/
@Autowired
private JdbcTemplate jdbcTemplate;
public void createtable(){
String sql="CREATE TABLE User(\n" +
" ID Long not null,\n" +
" NAME VARCHAR(20) NOT NULL,\n" +
" AGE INTEGER NOT NULL \n " +
" );";
jdbcTemplate.execute(sql);
}
@Override
public void create(Long id,String name, Integer age) {
jdbcTemplate.update("INSERT INTO USER (ID,NAME,AGE) VALUES(?,?,?)",
new Object[] { id,name,age });
}
@Override
public void deleteByName(String name) {
jdbcTemplate.update("delete from USER where NAME = ?", name);
}
@Override
public Integer getAllUsers() {
return jdbcTemplate.queryForObject("select count(1) from USER", Integer.class);
}
@Override
public void deleteAllUsers() {
String SQL = "delete from USER";
jdbcTemplate.update(SQL);
}
@Override
public void setDataSource(DataSource ds) {
}
@Override
public List<User> ListUser() {
String SQL = "select * from USER";
List <User> user = jdbcTemplate.query(SQL, new UserMapper());
return user;
}
/* public static void main(String[] args) {
UserService userService=new UserServiceImpl();
// Initialize the datasource, could /should be done of Spring
// configuration
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=UTF-8&characterSetResults=utf8&serverTimezone=GMT");
dataSource.setUsername("root");
dataSource.setPassword("199611@liu");
// Inject the datasource into the dao
userService.setDataSource(dataSource);
userService.createtable();
User user;
for(int i=0;i<100;i++)
{
Integer age=(int)(random()*30);
String name="liud"+age;
user=new User();
user.setId(new Long(i)).setName(name).setAge(age);
userService.create(user.getId(),user.getName(),user.getAge());
}
System.out.println("Now select and list all persons");
List<User> list = userService.ListUser();
for (User user1 : list) {
System.out.println(user1.getId() + " "+user1.getName()+" "+user1.getAge());
}
}*/
}
create table if not exists Ingredient (
id varchar(4) not null,
name varchar(25) not null,
type varchar(10) not null
);
create table if not exists Taco (
id identity,
name varchar(50) not null,
createdAt timestamp not null
);
create table if not exists Taco_Ingredients (
taco bigint not null,
ingredient varchar(4) not null
);
alter table Taco_Ingredients add foreign key (taco) references Taco(id);
alter table Taco_Ingredients add foreign key (ingredient) references Ingredient(id);
create table if not exists Taco_Order (
id identity,
deliveryName varchar(50) not null,
deliveryStreet varchar(50) not null,
deliveryCity varchar(50) not null,
deliveryState varchar(2) not null,
deliveryZip varchar(10) not null,
ccNumber varchar(16) not null,
ccExpiration varchar(5) not null,
ccCVV varchar(3) not null,
placedAt timestamp not null
);
create table if not exists Taco_Order_Tacos (
tacoOrder bigint not null,
taco bigint not null
);
alter table Taco_Order_Tacos add foreign key (tacoOrder) references Taco_Order(id);
alter table Taco_Order_Tacos add foreign key (taco) references Taco(id);
delete from Taco_Order_Tacos;
delete from Taco_Ingredients;
delete from Taco;
delete from Taco_Order;
delete from Ingredient;
insert into Ingredient (id, name, type) values ('FLTO', 'Flour Tortilla', 'WRAP');
insert into Ingredient (id, name, type) values ('COTO', 'Corn Tortilla', 'WRAP');
insert into Ingredient (id, name, type) values ('GRBF', 'Ground Beef', 'PROTEIN');
insert into Ingredient (id, name, type) values ('CARN', 'Carnitas', 'PROTEIN');
insert into Ingredient (id, name, type) values ('TMTO', 'Diced Tomatoes', 'VEGGIES');
insert into Ingredient (id, name, type) values ('LETC', 'Lettuce', 'VEGGIES');
insert into Ingredient (id, name, type) values ('CHED', 'Cheddar', 'CHEESE');
insert into Ingredient (id, name, type) values ('JACK', 'Monterrey Jack', 'CHEESE');
insert into Ingredient (id, name, type) values ('SLSA', 'Salsa', 'SAUCE');
insert into Ingredient (id, name, type) values ('SRCR', 'Sour Cream', 'SAUCE');
