spring data jpa 执行sql

最新推荐文章于 2024-08-07 21:08:36 发布

wyljz

最新推荐文章于 2024-08-07 21:08:36 发布

阅读量1.2w

点赞数 1

分类专栏： java 文章标签： spring jpa 数据库 sql

本文链接：https://blog.csdn.net/wyljz/article/details/52639255

版权

java 专栏收录该内容

83 篇文章 7 订阅

订阅专栏

Update.class，用于创建数据库表

import org.springframework.data.jpa.repository.*;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.jms.Session;
import javax.persistence.*;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by ly on 2016/9/23.
 */
public class Update {

    private EntityManagerFactory emf;

    @PersistenceUnit//使用这个标记来注入EntityManagerFactory 
    public void setEntityManagerFactory(EntityManagerFactory emf) {
        this.emf = emf;
    }

    public Update() {
//        entityManagerFactory = Persistence.createEntityManagerFactory("entityManagerFactory");
    }

    public void begin() {
        createTable();
    }

    private void createTable() {
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin(); //这个不加会报错

        Query query = null;
        for (int i = 0; i < sqlList.size(); i++) {
            query = em.createNativeQuery(sqlList.get(i));
            query.executeUpdate();

        }
        em.getTransaction().commit();
        em.close();
    }

    private List<String> sqlList = new ArrayList<String>();

    {
        sqlList.add("CREATE TABLE if not exists t_xfw_rights (\n" +
                "  id INT not null  AUTO_INCREMENT,\n" +
                "  rightName varchar(45) DEFAULT NULL,\n" +
                "  iconCls varchar(45) DEFAULT NULL,\n" +
                "  orderIndex INT DEFAULT NULL,\n" +
                "  fartherId INT DEFAULT 0,\n" +
                "  urlPath varchar(45) DEFAULT NULL,\n" +
                "  rightType INT DEFAULT NULL,\n" +
                "  PRIMARY KEY (id) \n"+
                ")\n");

        sqlList.add("create table if not exists t_xfw_operator(\n"+
                "  id INT not null AUTO_INCREMENT,\n" +
                " userName varchar(45) default null,\n"+
                " passWord varchar(45) default null,\n"+
                " userType int default null,\n"+
                " PRIMARY KEY (id),\n" +
                " UNIQUE KEY UK_username (userName)\n"+
                ")\n");

        sqlList.add("Create table if not exists t_xfw_operator_rights(\n" +
                " user_id INT(20),\n " +
                " right_id INT(20),\n " +
                " PRIMARY KEY (user_id,right_id),\n"+
                " KEY FK_right_id (`right_id`),\n"+
                " CONSTRAINT FK_user_id foreign key(user_id) references t_xfw_operator(id),\n"+
                " CONSTRAINT FK_right_id foreign key(right_id) references t_xfw_rights(id)\n"+
                ")\n");
    }
}

controller:

@Controller
@RequestMapping(value = "/")
public class ServerController {

    Logger logger=Logger.getLogger(ServerController.class);

    @Autowired//自动注入
    Update update;

    @RequestMapping(value = "/update")
    @ResponseBody
    public JsonResultObject update(){
        try {
            update.begin();
            return  new JsonResultObject(EnumState.Ok,"ok");
        }
        catch (Exception e){
            return new JsonResultObject(EnumState.Fail,e.getMessage());
        }

    }

}

applicationContext.xml：

    <!-- JPA实体管理工厂的配置 -->
    <bean id="entityManagerFactory"
          class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="jpaVendorAdapter" ref="hibernateJpaVendorAdapter"/>
        <property name="packagesToScan" value="com.jiapeng.xfw.server"/><!--待扫描的实体类包，不再需要persistence.xml了 -->
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.formate_sql">true</prop>
                <prop key="hibernate.hbm2ddl.auto">none</prop>
                <prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.DefaultNamingStrategy</prop>//这个设置是让列名采用自定义的名字，免得在使用jpa进行数据库操作时生成的sql的字段名都是像 user_id 这样加下划线的名字
            </props>
        </property>
    </bean>

 <beans>
        <bean class="com.jiapeng.xfw.server.normalClass.Update">
            <property name="entityManagerFactory" ref="entityManagerFactory"></property>
        </bean>
    </beans>